Thank you very much!

Aww.. thank you.

Great to hear!

Glad it was helpful!

​​@@chandoo_ How to open a password protected PDF file with power query as mostly bank statements are protected with a password?

Thanks man :)

Thanks Wayne... I am glad you will be using these ideas soon.

Wow, thank you!

You Bet. I have been using this trick for more than 2 years now (with Power BI route) to save time on gnarly data compilation situations.

Glad to hear that

Glad you think so!

My pleasure Gagan...

Thanks Amit. I am always adding videos on all these topics. On Power Query I have a full 90 minute class that goes in to great details - see it here kzfaq.info/get/bejne/hs92dMSQz9DFdX0.html Will be adding a detailed video on DAX & Power Pivot in the month of May or June.

@@chandoo_ thank you so much for your quick reply i will definitely view and learn Power query video. Thank you so much for taking us in advanced level of excel. Keep learning and keep sharing thank you

Thank you Noob.. 😀

You are welcome Max :)

My pleasure!

Always welcome

You can customize which tables are picked by editing the query and going to "transform_sample". If you think what you need is either table3 or table4, extract both and use Power Query filters or some other calculation to figure out which ones to include.

Glad you like it!

Yes, exactly

My pleasure...

Glad you liked it!

I will.

Thanks Tracey. I show another technique (with Excel files, but works for PDF too) in my recent video. See it here and apply the concept to PDF. kzfaq.info/get/bejne/ocWlmtaZr5rbeas.html

Boom!

@mrprakashraobs I have a similar problem, but in my case, I have multiple invoices that look identical, yet some have 4 tables and others have 3 tables. In each I have to select the table that is one less than the max table no, so where there are 4 tables, I need to select Table003 and where there are 3 tables, I need to select Table002. Any advice on how to achieve this?

Consistent formats is the key. If your invoices have different layouts then automating them can be tricky (but not impossible). Try customizing the "transform sample" to tweak the outcomes.

@@chandoo_ I will try and let you know the outcome soon. Thanks for the tip.

Thank you Sai... I will keep this in mind for future videos.

You need to apply some additional transformation steps to extract this. I suggest learning a bit more about PQ. See this video for a start - kzfaq.info/get/bejne/hs92dMSQz9DFdX0.html

See this page for more information how to use Power BI - chandoo.org/wp/powerbi-introduction/

@@chandoo_ Thank you so much Sir ... I'll go through the same ....

Thanks Pooja. VBA cannot record actions performed outside Office apps. As you would be dealing with PDF, this won't work. You need to use either 365 or use other tools such as Power Automate or Power BI.

Wow... that is awesome to hear. You can customize the *transform sample query* to do this. It is tricky to explain in a comment. I suggest seeing this article - chandoo.org/wp/combine-excel-files-using-power-query/

@@chandoo_ thanks a lot!

I understood the question to be, "How to do multiple tables within the same file?", not multiple spreadsheets. I have the same question because it only allows us to pick one table at a time.

You can use Power Query to combine the data in multiple files to one and then use the "Save as PDF" option in the file menu. If you want to automate or need to combine sheets that have charts etc, then you can use VBA.

Thanks for the feedback. I am changing the audio process now and except to have better volume in future videos

You are welcome Garry. I think there might be some invisible characters in the PDF which are causing the trouble. You can backtrack the Power Query steps and see where the culprit is. Alternatively, you can split the table to two chunks (one with correct data, one with right shifted rows) and adjust the issue and append both. I am unable to look at the file as I have a busy few weeks ahead. Please try the above suggestions and if the problem happens again try posting for help in Stack overflow or something. 😀

Unfortunately, this feature is not capable of OCR (optical character recognition). You would need special software or web tools for that.

@@chandoo_ yes sir. Thank you.

I am doing some accounting from bank statements. I have fooled around with scanning and OCR. I was given a kodak scanner and it works fine in most cases. The exception is Scotiabank, where they put a dotted line where the decimal is.

Hi Alter, Thanks for your comments. You can do it like this: 1) Follow the video process for one table for all PDFs in the folder 2) Once the query is working and you get what you want, just repeat the process for other tables 2.1) you can do it by doing all the steps again. If you have just 2 or 3 more tables, do this. 2.2) you can duplicate the "transform sample" query and create new types of transform functions to extract the tables. This requires knowing a little bit more of Power Query. So do it if you have lots of tables or just want to learn. I can't explain the process here, but do check out my Power Query mini-course where I talk about the advanced concepts - chandoo.org/wp/power-query-power-course/

Great suggestion! Unfortunately, as of now none of the PDF import options are capable of OCR. May be in future.

Sorry, but as far as I know, Power Query for Mac Excel doesn't yet have PDF option. May be in future...?

Thanks Pratik... At the moment PDF options in Excel and Power BI do not support image recognition feature. But you can use Excel app on your phone to scan documents and load data to Excel. Save the file and open it on PC to use such data. I will make a tutorial on this soon.

You are welcome VV. Chandoo is my nickname. It means moon in my native language.

@@chandoo_ is youtube allows to create channel in nick name..????😂😂😂 n ur profile pic is ur younger version..???

Yes and yes.

@@chandoo_ there used to be a tv ad of electric switches..in which a guy when puts his finger in electic switch gets his hairs high like ur profile pic..seeing ur profile pic..that ad comes to my mind..😂😂😂

Give it a try. I suspect, it wont.

Thanks 👍 It is my favourite. Got it from Legoland Günzburg when we visited it in 2019.

Hmm.. Are you sure there is such a table in that file? When you use "folder" option and pick a table based on one of the files, if PQ can't find the same table in all files, it will give this error. I will examine each of the files to make sure there is no problem. Another issue is if you have other files too in the folder instead of just PDFs. If so, try adding a filter condition at the front, as explained here - chandoo.org/wp/extract-data-from-pdf-to-excel/

@@chandoo_ how to know that excel can convert that pdf properly, actuall i am downloading marksheet from online and then saving that page as pdf, i think that might be problem, please provide solution to this problem, with your excellent reply as always

Once you have the data in Power BI, you can open the Query Editor and then copy paste data to Excel.

Interesting. 1) Connect to the PDF 2) Select any one table in the "navigator" screen 3) Go to PQ Editor, delete all steps except Source 4) At this point PQ will list all tables. 5) Filter out anything you don't need 6) Then use the column "expand" button to expand and combine all tables to one big table.

Thanks Petr. I suggest using Append option to combine two pages to one table. I am not so good with replying to emails. I suggest checking out my PQ video and figuring the necessary steps out. Intro to Power Query video - kzfaq.info/get/bejne/hs92dMSQz9DFdX0.html