Importing Bank Statement PDF into Excel via Power Query

[@peppleibitoru7069]

So far the best explained video without any bias

[@mustaro7792]

Interesting. Nice sharing. 👍🏼

[@pvspkumar3065]

Very well explained without any ambiguity and the presentation is excellent to follow and understand

[@JaiRaman-tc6zr]

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

[@jfernandez7410]

Perfectly explained. Direct to the point. I am your new subscriber

[@rssdharini]

Excellent..👍👍👏👏 Dhanajay .. Thank you .. keep educating us.. please..

[@sujesh.818]

Excellent Dhanjay ji. Will ask any clarification is needed

[@CSMink-q2l]

Well done. Thanks very much.

[@shokhabbosmaksudov4618]

Bro, you lost info from the description when sorting null date)) Anyway, I found what I was looking for. Thank you

[@ExcelKida]

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.

[@DeanTrethewey]

Your rock! that was super helpful!!!

[@bhagirathdhoot6537]

Superb...

[@mahendrab8810]

If I want to merge multiple line narration datewise in pq how to do it in power query

[@ExcelKida]

I already covered this scenario in this video. Check out below video kzbin.info/www/bejne/bZLZkKWfgdONhNk

[@vouficarriconapaz7219]

Thx for sharing 😄

[@atreyaram]

Excellent

[@Amitkumar-nd9fm]

it is worked on credit card statement also...plz tell sir

[@ExcelKida]

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.

[@Amitkumar-nd9fm]

@@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

[@khandavadeveluaravindhandc3784]

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.

[@ExcelKida]

Ideally this trick should work with multiple pages.

[@khandavadeveluaravindhandc3784]

@@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.

[@adityamundada6132]

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 ?

[@ExcelKida]

Simply update your MS Office, it will appear

[@BaffaBadamasi]

i use a mac book pro it doesn't show the pdf option on my 365

[@ExcelKida]

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.

[@priyamvadaparmar]

How to import a file with more than 50 pages... Please help

[@ExcelKida]

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.

[@mzms00]

How to import narration also

[@ExcelKida]

You can copy paste remarks into narration column

[@Nazar_ali_choudhary]

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?

[@ExcelKida]

Yes, using Append option But you need to ensure that you have exactly same columns for all tables, before performing append.

[@khanifshana3504]

​@@ExcelKidamy statement has no margins it's all messup data what should I do

[@khanifshana3504]

And there are at least 360 pages

[@khanifshana3504]

​@@ExcelKidaplz help

[@ExcelKida]

For such a high volume, this method is not suitable. Go for some PDF to Excel conversion tools.

[@ama6416]

But I can't SUM the amount of money of the data that converted from pdf. Does anyone know what is the problem?

[@ExcelKida]

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.

[@ama6416]

@@ExcelKida Thank you very much, you're so helpful.

[@Faisal-qy6fp]

I didn't find PDF option on my excel sheet "From File"

[@ExcelKida]

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.

[@krishnakantgoyal521]

Agar get data ka option hi na show ho rha ho Excel 10 me

[@ExcelKida]

That means you have very old version of Excel. I will suggest you to upgrade to Office 365 version of MS Office

[@khethothedisi1702]

If it's password encrypted how do i import to excel

[@ExcelKida]

Then you need to use Print to PDF printer to generate password-less file & then go for the steps I demonstrated

[@mariankeen8591]

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.

[@ExcelKida]

I covered this scenario in another video. Sharing link of it kzbin.info/www/bejne/h3TGqYSlmKilrtk 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

[@matttzzz2]

@@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

[@ExcelKida]

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.

[@elizabethanndodd2326]

@@matttzzz2 same

[@odolasejalraj9292]

How about the Connection

[@ExcelKida]

Can you deliberate your query in a bit more detail ?

[@GhulamKarim-s9u]

BROTHER IN MY CASE I HAVE MORE THEN 200 SHEETS HOW I CAN CONVERT IT, ITS A HEAHACHE

[@ExcelKida]

Such massive data this trick is not gonna work. You will have to try PDF to Excel paid software for this task.

[@gstshiva4181]

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

[@ExcelKida]

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).

[@sukantamondal8853]

Windows 10 main Get data option nehi hai, kaise laia

[@ExcelKida]

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

[@Amitkumar-nd9fm]

plz add space between narration sir

[@ExcelKida]

Some cosmetic changes you can do via Excel

[@vaishakhc3101]

It shows "pdf failed to load with result : password Required"

[@ExcelKida]

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

[@FAYR71]

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?

[@ExcelKida]

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

[@FAYR71]

@@ExcelKida thanks for your reply. I look forward to seeing the video.

[@ankitmkgupta]

did you make any video for this?? @@ExcelKida

[@ShahAnkur-c6u]

Sir excel to tally pe vedio create kijiye

[@ExcelKida]

Video बनाये तो है मैने, आप एक बार channel video list मे check करिये, आपको मिल जायेंगे

[@ShahAnkur-c6u]

@@ExcelKida Please send me vedio link

[@ExcelKida]

kzbin.info/www/bejne/hGWvhZineq-WaLc

[@ShahAnkur-c6u]

@@ExcelKida sir aeto tally prime 4 he

[@ShahAnkur-c6u]

@@ExcelKida Tally XML file kese banaya

[@StevenAnPham]

Bro need some sleep..

[@ExcelKida]

Sure. You can email / WhatsApp me your query on email ID / number I have shared at the end of video.

[@premlata7053]

FB link

[@ExcelKida]

facebook.com/excelkida/

[@AnandGautam9901]

Not Useful when multiple pages of Bank statement 😅

[@ExcelKida]

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

[@adithyets4631]

while in navigator tab i am getting table is empty

[@ExcelKida]

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

[@bhakshamb2102]

​What is the remedy for that problem ​@@ExcelKida

[@telanganagamezone5238]

I am not getting the preview. Please help me out

[@ExcelKida]

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