How to "automatically" extract data from a messy PDF table to Excel

Ever had a gnarly PDF that you need to extract data and reshape? Today let me share an advanced Power Query trick with you. You can use this to reshape (split & combine) PDF tables. Later on in the video, I explain how to build a CUSTOM FUNCTION in Power Query so you can automate the task next time. Don't miss the ending!
📁Sample files:
Try it yourself on the PDF 👇
chandoo.org/wp/wp-content/upl...
Completed Excel File with PowerQuery transformations + custom function 👇
chandoo.org/wp/wp-content/upl...
⏱ IN THIS VIDEO:
0:00 - The PDF Table Problem
0:47 - Getting the data from PDF
1:40 - Power Query Parkour Time 🪑🏃‍♂️
4:16 - Creating a PQ Custom Function to split and combine tables
7:00 - Invoking the function on your PDFs
8:42 - But there is more...
👩‍💻 MORE VIDEOS ON POWER QUERY
Learn Power Query in 15 minutes - • Learn Power Query & Au...
Complete Master Class on Power Query (90 mins) - • Power Query Tutorial -...
Combine PDFs with Power Query - • How to Combine PDFs to...
Combine Sheets with Power Query - • AWESOME Excel trick to...
Combine Workbooks with Power Query - • Powerful trick to comb...
Multiple Find Replace with PQ - • Multiple Find / Replac...
Power Query - Automating 5 Data Tasks -
• FREE Power Query Mater...
Power Query Playlist - • Power Query How-to & Tips
Power Query Mini Course - chandoo.org/wp/power-query-po...
~
Why did the PDF break up with his girlfriend?
Because she is not on the same page 😂
#powerquery

[@RajKumar-hw4bt]

Great and Yes to see how the entire tables is clubbed into one.

[@RomantiCynic]

Another great video, Chandoo, thanks! Definitely, yes, interested in the bigger Power Query project!

[@moisestatis5418]

Thank you Chandoo and a BIG YES to the following videos.

[@the_creator99]

Yes! Need the video! Have been looking for it to combine multiple pdf tables into power query.

[@ahmed007Jaber]

Yes Interesting always find tips about working with pdfs are worthwhile

[@lovelittlepea96]

Yes, please share your way of dealing with it. Thank you so much for sharing :D

[@medher3593]

Amazing work Chandoo, YES, I would love to see how you solve the entire problem

[@kazimuhammadyakubali340]

yes we are eagerly to watch the next video of Power quarry

[@sanjayjha1966]

yes, thanks for such a valuable vid.

[@samdeepak]

Yes, I'd love to see the next part of it.

[@JavedAnsari-ue5td]

Thanks for sharing the knowledge Sir, yes I am interested to see how these tables can be combined

[@user-od5fq9kq8e]

Interesting topic, thanks for Sharing the information.

[@chrism9037]

Excellent trick Chandoo!

[@Luciano_mp]

*Yes...* Thank you Chandoo!

[@lukev730]

Yes Chandoo… you are awesome

[@trevorbbeairsto]

Thanks for this! Yes for more 👍

[@charanreddys7190]

Today I learnt about creating and calling the function.. love you boss ❤

[@chandoo_]

Happy to help

[@stevenreeve6139]

Another great video. Yes, I would like to see all tables in the entire document in one dataset.

[@carocajiao]

Yes, I would love to see that. Thank you

[@rxepdmgm9523]

Yes, We would like to see that option. Great 👍🏻

[@irfanahmad-it2bp]

Awesome work and yes would love to see the next video.

[@AnbarasuAnnamalai]

Yes, want more. Interesting.

[@mindyourown6320]

Yes! Thank you!

[@jc1866vzla]

Yes, please. Excellent video

[@abhinav10x]

Thanks alot..and Yes for next Learning about this.

[@santoshkiran5858]

Yes Very instrumental information ❤❤❤

[@outsideedge7999]

Yes please make a video on the entire PDF

[@user-xm4bz8st8r]

Good and clear explanation 🎉

[@rajeshmajumdar4999]

Yes, Thanks Chandoo!

[@vanderse01]

great video and yes please provide another video to combine all pricelists

[@sanjeevsoni6793]

Thanks Candoo, yes

[@sandeeppaib1985]

Great Chandoo.... Loved it

[@GauravRaj-nq6jl]

BIG YES Help Full Content

[@shedizon5978]

Yes!and Thank you Sir!

[@Nic_Holas]

great work, thanks for sharing

[@umamaneiramelhor]

Big YES! 🎉

[@georgemalinakis8522]

Yes Please Chandoo. Thanks being supported

[@gerohazigabor]

Yes, it would be interesting to learn that. 😃

[@rxepdmgm9523]

Magnificent 👍🏻

[@Sekoleyte]

Yes of course sir ✌🏻 thanks in advance

[@sheevamchakraborty1257]

Yes, waiting for the upcoming video

[@SameerShaikh-1992]

Yes, Chandoo love to have that video

[@alanmonaghan9194]

Yes. I'm trying to do this with PDF forma bank statements, but it is getting a bit complicated due to some columns not aligning with the rest and various other stray data.

[@bchy78]

Yes plz, awaiting to watch the vdo...

[@barttitulaerexcelbart9400]

Yes, thank you.

[@maheshs6098]

Yes, interesting & useful....

[@saikrishnanimmagadda6469]

Yes, Chandoo!!

[@illiakhmaladze2071]

Definitely yes, Chandoo 😊

[@vishalasari26]

yes. Would love to see

[@rDinx]

Yes please, this is similar to a project I am currently working on.

[@zhuzhu9599]

Definitely... Yessss!

[@aman_mashetty5185]

Yes!! Need a video

[@rajkumargerard5474]

Yes Sir I would like to see how and what steps you applied to achieve it..

[@laksdjf09]

YES - and thanks

[@idhingra6360]

Yes, would like to know this technique.

[@aravindram953]

Yes and make a video for the entire pdf file.

[@alexk8343]

Yes, sir! :)

[@KartikSir_]

Yes from Heart of India,

[@alexrosen8762]

YES!

[@arbazahmad7177]

Great 👍 "Yes"

[@miguelcalejo1770]

Yes, of course.

[@yohannesteshome2043]

Yes chandoo

[@how2excel999]

yes please, very good

[@sagarkeshyal752]

yes sir please make a video of the advance of it

[@ramasubramanianh9347]

Yes definitely

[@sabbasachisaha]

Yes❤

[@jemusudebiddo1774]

Yes!

[@bodapudinikhil505]

Yes chandoo garu

[@TheUmaiz]

👍 Yes

[@katow72]

YES

[@nicor1501]

that will be a 'YES' for me as well 🙂

[@sreyabasak9677]

Yes

[@pratikghate5370]

Yes please make the video

[@saadbassy673]

Yes mister !

[@mauryaparkara124]

Yes we want to see combined data set of the PDF

[@fahadamir6655]

Yes ofcourse.

[@alfalogic]

absolutely yes 🤑

[@ankursharma6157]

YES! YES!! YES!!!

[@silaspaul144]

Yes.

[@muhammadamjad8009]

yes. Make a video on this.

[@sayakdasgupta1788]

Nice one, if possible then share the pdf file so that we can practice while watching your videos.

[@chandoo_]

I already did. Please check the video description.

[@karan007in]

Yes please make a video

[@mdcoffeanan2838]

yes

[@bibekkoirala5294]

Nice one. Does it also work for another excel file as we have already made a function?

[@chandoo_]

You will need to add the function to other files (just copy the code from Advanced Editor), create a new blank query and paste the code in Advanced Editor.

[@bibekkoirala5294]

@@chandoo_ thank you! I will try it. Whenever you upload the next video plz show it also.

[@jayceeharb1305]

yes!!!!

[@Skopoxod]

Yes, please.

[@asterryan]

hi, pleasant day. i hope the video for the whole pdf conversion is finish. may i know the link? thank you very much in advance :)

[@johnford3825]

Yes please.

[@Bhavik_Khatri]

Yes, like to new video. It feels like List.Transform and List.Combine PQ functions need to be utilise with your custom function.

[@tofikqureshi6507]

Hello Sir Pls help me I'm facing the one condition use Google sheet make pivot table need click pivot table showing data as same sheet not new sheet data visible same sheet pls❤help

[@michaelbrown8821]

Yes^2

[@Its.my.pleasure]

👌👌

[@namrathajain5194]

Yesssssss

[@naveenranga8493]

Yes anna

[@taizoondean689]

Yes Sir Please create Video on PDF

[@julianscantlebury4858]

Yes please

[@maheshkumar-wn5vv]

Yes, from hyd telangana

[@nithins4240]

Yessssssssssssssssss

[@Slang38]

👍🏻

[@andrewsinha2785]

Yes Please