No, didn't lost it, rather I deliberately removed it. There is another video, where I have demonstrated, how to combine & retain multi line description.
@@ExcelKida this helped me convert bank statements with multiple pages that are automatically considered as a single table. But in certain bank statements the pages show as different tables. I want know how to merge those multiple tables that essentially have similar data and is in same order. Pls help.
I have covered multiple pages import in another video. But 50 pages is pretty voluminous. Adobe offers a special discounted plan for PDF to Excel conversion, which is pretty cheap (I guess ₹ 1500 / year). Go for it to perform such bulk level pages conversion to Excel.
Microsoft never show up interest in pushing things for Apple, due to the rivalry. Even their official support page too reflects that PDF is not available for Mac OS (refer below link). support.microsoft.com/en-us/office/import-data-from-data-sources-power-query-be4330b3-5356-486c-a168-b68e9e616f5a#ID0EBF=macOS My personal suggestion, use AI to get this job done, which is far more intelligent than Excel in such things. I have seen PDF import option failing for many bank statement PDF.
I covered this scenario in another video. Sharing link of it ruclips.net/video/QCcxQocCrxw/видео.html But I think 124 pages is too much large even for this technique. You should try to grab CSV / text delimited file for this from your banking menu
@@ExcelKida Unfortunately both of my banks only provide 2 years worth of CSV data, anything older than that is provided as PDF. I'm calling my bank to request older CSV files
Sorry to hear this. Generating the CSV file is very light weight for any banking system compared to PDF which is bulky. So logically, it is a crazy response by the bank. Sometimes I have seen non technical users are unaware of the term CSV. Try asking for a flat file or fixed width format file, which is supported by the majority of banking systems. I have even covered how to import bank statement in Excel from a flat file in one of my video.
Just ensure to apply Decimal format in Power Query to columns having numeric / amount type of data. SUM function fails if in the given range text is found in any cell. Sometimes cell appears as number but internally comma symbol might be treated as text when loading from Power Query.
Possible issues & troubleshooting 1) your Office 365 Excel is not updated [go to File > Account > Update Now] 2) you are using Excel version other than Office 365, like 2007,,2013,2016,2019, which does not support this feature I would suggest go for Excel Online which you can sign-up for free & use this. I have already demonstrated this in latter portion of video.
GOOD MORNING SIR, HOW TO REMOVE HEADER (LIKE FIRM NAME , ADDRESS , BANK ADDRESS AND MORE ) ITS COME EACH PAGE , HOW TO REMOVE IT , ITS KOTAKMAHENDRA BANK , PLZ REPLY ME , I AM A UR SUBSCRIBER , THANK U
You need to use Power Query "Remove Rows" operation in Home tab of Power Query, where it asks you how many rows to remove from top (for page headers) or bottom (for page footers).
Would not work with password protected PDF. You can try a simply hack. Just open PDF via Acrobat Reader & then use Print to PDF inbuilt printer of Windows to generate PDF, which effectively removes your password in result file. Then you can follow the step of videos
Hi, I have a question about the 2nd column (Narrative). If I want to keep the description which goes over 2 lines and combine them in one cell. Can you explain to me how to do this?
Yes you can, but the steps are very complex for that. Also, it requires writing some of the M Query script manually. But anyway, thanks for this input. I will definitely plan a video for this too for future
For multiple pages, you need to use Append option to combine multiple pages & then do the manipulation. If you develop some hands-on on playing with Power Query, then even multiple pages easily
Sometimes, table is not formatted properly, due to which Power Query might not be able to decode data into tabular format, which would display table as empty
Possible issues 1) If you don't have Office 365 Excel, you will not get PDF option 2) Power Query will might not be able to decode every PDF into table
So far the best explained video without any bias
Very well explained without any ambiguity and the presentation is excellent to follow and understand
Ty u r only till now I have seen hundreds of videos of excel 2 mention the excel version used ty keep up the good work
Perfectly explained. Direct to the point. I am your new subscriber
Excellent..👍👍👏👏 Dhanajay .. Thank you .. keep educating us.. please..
Interesting. Nice sharing. 👍🏼
Well done. Thanks very much.
Your rock! that was super helpful!!!
Excellent Dhanjay ji. Will ask any clarification is needed
Thx for sharing 😄
Excellent
Superb...
Bro, you lost info from the description when sorting null date)) Anyway, I found what I was looking for. Thank you
No, didn't lost it, rather I deliberately removed it. There is another video, where I have demonstrated, how to combine & retain multi line description.
Hi. I have a file with more than 10 pages. Is there a way by which I can clean all these pages and load to a single excel sheet? Please help me out.
Ideally this trick should work with multiple pages.
@@ExcelKida this helped me convert bank statements with multiple pages that are automatically considered as a single table. But in certain bank statements the pages show as different tables. I want know how to merge those multiple tables that essentially have similar data and is in same order. Pls help.
How to import a file with more than 50 pages... Please help
I have covered multiple pages import in another video. But 50 pages is pretty voluminous. Adobe offers a special discounted plan for PDF to Excel conversion, which is pretty cheap (I guess ₹ 1500 / year). Go for it to perform such bulk level pages conversion to Excel.
Hi sir,
i have a bank statement with multiple pages & power query makes a separate table for each page, is there any way to merge all of those tables?
Yes, using Append option
But you need to ensure that you have exactly same columns for all tables, before performing append.
i use a mac book pro it doesn't show the pdf option on my 365
Microsoft never show up interest in pushing things for Apple, due to the rivalry. Even their official support page too reflects that PDF is not available for Mac OS (refer below link).
support.microsoft.com/en-us/office/import-data-from-data-sources-power-query-be4330b3-5356-486c-a168-b68e9e616f5a#ID0EBF=macOS
My personal suggestion, use AI to get this job done, which is far more intelligent than Excel in such things. I have seen PDF import option failing for many bank statement PDF.
Very good video. However I am not getting an option to import pdf even if I have office 365 subscription. How can I do this function in that case ?
Simply update your MS Office, it will appear
If I want to merge multiple line narration datewise in pq how to do it in power query
I already covered this scenario in this video. Check out below video
ruclips.net/video/7av_riLnZNw/видео.html
I struggled to do this because my statement converts to 124 Tables. There are about 5800 lines of data. I am not sure where to go from there.
I covered this scenario in another video. Sharing link of it
ruclips.net/video/QCcxQocCrxw/видео.html
But I think 124 pages is too much large even for this technique. You should try to grab CSV / text delimited file for this from your banking menu
@@ExcelKida Unfortunately both of my banks only provide 2 years worth of CSV data, anything older than that is provided as PDF. I'm calling my bank to request older CSV files
Sorry to hear this. Generating the CSV file is very light weight for any banking system compared to PDF which is bulky. So logically, it is a crazy response by the bank.
Sometimes I have seen non technical users are unaware of the term CSV. Try asking for a flat file or fixed width format file, which is supported by the majority of banking systems. I have even covered how to import bank statement in Excel from a flat file in one of my video.
But I can't SUM the amount of money of the data that converted from pdf. Does anyone know what is the problem?
Just ensure to apply Decimal format in Power Query to columns having numeric / amount type of data. SUM function fails if in the given range text is found in any cell. Sometimes cell appears as number but internally comma symbol might be treated as text when loading from Power Query.
@@ExcelKida Thank you very much, you're so helpful.
I didn't find PDF option on my excel sheet "From File"
Possible issues & troubleshooting
1) your Office 365 Excel is not updated [go to File > Account > Update Now]
2) you are using Excel version other than Office 365, like 2007,,2013,2016,2019, which does not support this feature
I would suggest go for Excel Online which you can sign-up for free & use this. I have already demonstrated this in latter portion of video.
Agar get data ka option hi na show ho rha ho Excel 10 me
That means you have very old version of Excel. I will suggest you to upgrade to Office 365 version of MS Office
GOOD MORNING SIR, HOW TO REMOVE HEADER (LIKE FIRM NAME , ADDRESS , BANK ADDRESS AND MORE ) ITS COME EACH PAGE , HOW TO REMOVE IT , ITS KOTAKMAHENDRA BANK , PLZ REPLY ME , I AM A UR SUBSCRIBER , THANK U
You need to use Power Query "Remove Rows" operation in Home tab of Power Query, where it asks you how many rows to remove from top (for page headers) or bottom (for page footers).
it is worked on credit card statement also...plz tell sir
It totally depends on the internal structure of the PDF file & if Power Query is able to decode it nicely or not. You can always give it a try.
@@ExcelKida sir can you solve problem of add space between narration in Excel ... Do you have any formula for that..plz help sir how to do space
It shows "pdf failed to load with result : password Required"
Would not work with password protected PDF. You can try a simply hack. Just open PDF via Acrobat Reader & then use Print to PDF inbuilt printer of Windows to generate PDF, which effectively removes your password in result file. Then you can follow the step of videos
If it's password encrypted how do i import to excel
Then you need to use Print to PDF printer to generate password-less file & then go for the steps I demonstrated
Windows 10 main Get data option nehi hai, kaise laia
Windows 10/11 had nothing to do with this. It seems you have an outdated version of MS Office. Kindly upgrade to Office 365 to enjoy this feature
Bro need some sleep..
Sure. You can email / WhatsApp me your query on email ID / number I have shared at the end of video.
How about the Connection
Can you deliberate your query in a bit more detail ?
How to import narration also
You can copy paste remarks into narration column
Hi, I have a question about the 2nd column (Narrative). If I want to keep the description which goes over 2 lines and combine them in one cell.
Can you explain to me how to do this?
Yes you can, but the steps are very complex for that. Also, it requires writing some of the M Query script manually. But anyway, thanks for this input. I will definitely plan a video for this too for future
@@ExcelKida thanks for your reply. I look forward to seeing the video.
did you make any video for this?? @@ExcelKida
plz add space between narration sir
Some cosmetic changes you can do via Excel
Sir excel to tally pe vedio create kijiye
Video बनाये तो है मैने, आप एक बार channel video list मे check करिये, आपको मिल जायेंगे
@@ExcelKida
Please send me vedio link
ruclips.net/video/N4LTeqEJc2U/видео.html
@@ExcelKida sir aeto tally prime 4 he
@@ExcelKida
Tally XML file kese banaya
FB link
facebook.com/excelkida/
Not Useful when multiple pages of Bank statement 😅
For multiple pages, you need to use Append option to combine multiple pages & then do the manipulation. If you develop some hands-on on playing with Power Query, then even multiple pages easily
while in navigator tab i am getting table is empty
Sometimes, table is not formatted properly, due to which Power Query might not be able to decode data into tabular format, which would display table as empty
What is the remedy for that problem @@ExcelKida
I am not getting the preview. Please help me out
Possible issues
1) If you don't have Office 365 Excel, you will not get PDF option
2) Power Query will might not be able to decode every PDF into table