Wow! You have a way of designing these examples and walking us through them that deserves a Teacher-of-the-Year award. Possibly also a Medal of Freedom. We all owe you a big thanks!
Thank you, Leila! You're the best! After watching several other videos on this topic that were too complicated, too wordy or just "too-something", I discovered your video. What a breath of fresh air! Concise and successful! Can't ask for anything more than that. Your video helped me to accomplish exactly what I needed it to do. Again, thank you!
Thank you SO SO much. People at work think it's a good idea to save their spreadsheets as MULTIPAGE PDFs and send them around the org instead of just sending the Excel file.... now I won't need to spend so much time making them an Excel file again.
Holy Moley! I am thinking about how much time this would have saved me on a project I did back in 2007. This is amazing! Thank you so much for sharing!
I'm glad I found this channel. I've saved MS Excel Playlist and planning to watch all videos very soon. High quality content is highly appreciated. I will support this channel. From India ❤
This is awesome. I would normally combine the PDFs in Adobe Pro and then export them as an excel, but that's a one-time solution. Having the ability to refresh the file as more PDFs are added to the folder makes the process look much more streamlined.
At the 9:00 minute mark " ... let me know if you have ever imported files like this into Excel ...". No ... not yet. However, I have been thinking of doing something similar with many years of historical data. My concern is that the pdf line items and headings may change from year to year. Once again ... another intriguing lesson from Prof. Leila. Thank you!
It does have some limitations, but as long as there is a logic to the headers you can add PQ steps. You could for example add a mapping table for the headers or find a dynamic way to pinpoint the first row to import. If you do end up using this feature let me know how it went.
Thank you Leila Gharani, very interesting and knowledgeable videos of Excel are being shared by you. I always recommend your channel to my training participants in Afghanistan.
This is awesome.. A big thanks for your time and efforts. For me this task was impossible until I watched this video. May you live long in good health and peace so that you can enlighten us with your latest contents in excel. Always love to spend time to watch your videos. Thanks once again.
Leila I would like to suggest a feature I found on power query. It is the ability to anti-join two queries, in order to exclude items that exist in one of them from the other. This was brilliant for me cause I had a list with all my currently open orders and one list with the orders that got invoiced today and would get updated during the nightly system procedure. This would be simple if all entries in both lists were unique, but since the lists had orders with their products, they had the order number multiple times for each product. So appending the two lists and removing duplicates was a no no. Power query came to the rescue and not only it can exclude items that exist in one list from another, but now I can automate it. I can just provide the files with the entries and the managers, that need to watch the order fulfillment closely, have just to hit refresh.
Hi Leila, …I loved this demonstration, once again you taught me something I through I knew already. For you to have a smile: I was used for years doing this process by making my first file query, then advanced edit to change it into the function I will invoke against the filtered files query. Then recently, I saw this help query. Saying myself, what’s that noise? I didn’t ask for, it’s weird It looks like something I need but didn’t know how to use it, so I trashed it all, and redo it my own old way of doing. …ridiculous, no? As you already mentioned this tool is one of the best productivity and solid tool I have ever used for years in the area, tremendous, powerful, fit to a large range of users, from basic to advanced. Thank you for the knowledge sharing
Very helpful many thanks for your channel. I hit a snag. The table did not translate all columns correctly. My work around was to use page then use functions like column type and text filters to get to my goal. A great useful channel and this feature will save me hours.
Thanks for sharing. Definitely handy, but a core assumption is that data needs to one level of rows and columns. If not, excel cannot handle multiple levels of rows and columns. For instance, and XML, which has n-depth, cannot be easily parsed by this built-in utility in excel. One needs to code it to handle multi levels
Excellent vid. The thing about filtering for the sample file was very good - not very obvious (to me, at least!) when you're looking at an error and thinking you've already filtered to get just pdfs! Thanks!
With great power query comes great responsibility to watch your fantastic training video to learn its proper use. I like it when software is intuitive and I can figure things out but there is no way I can account for all the great features you point out.
This video is fantastic! Thank you so much for creating it. I have a question, my original PDF files have multiple pages, hence, PowerQuery created multiple tables in the "Combine File" dialogue. How do you select multiple tables to be included in the sample file, as if I don't, the combined file will only have the first page of each file. Thank you!
True I actually have some structured and unstructured PDFs I’m able to extract the table I want for structured using python but I’m unable to find something for unstructured (basically the ones you can’t copy text from) does anyone have any solution also if someone wants the code for the python extractor lmk.
OMG! Every reporting period I have to go through a client PDF report with multiple pages that is a complete mess. I didn't know that I could import the pdf file to Excel using Power Query so I can manipulate as needed. This is a huge time saver!! Thank you!!
Most mind-blowing thing learnt from this is excel file will be prioritised over pdf files as first file in PowerQuery. I would be doomed and might be furstrated to start the whole project over again.😱
Very nice. I tried this and works very fine with single page pdf files or meaning excel extracts the information from one table in the page which one choses in the sample. I have been trying to bulk extract different types of table information from multiple page pdf files. Let us say in one page there are several tables in a multi page pdf and you have many pdf files which have the same table type inside. Is it possible to import all tables from all pages and from all pdf files at once? It would be very helpful if you do one video on with such examples 😉
I am so happy until the last sentence that this feature is available only from 2021 and OF 360. Thank you so much anyway for sharing this excellent lecture!
i do this all of the time. very few if any of my colleagues know this. i convert pages of monthly brokerage investment statements into excel while everyone else is typing entry line by line.
Hi Leila ... The video got me excited as I was struggling to find a way to convert PDF data into excel. I am using Excel 2016 but in power query i saw options were limited to Cvs , text & Excel itself but no PDF , consequently I could not convert the data. Is there i way around this problem ?
I was so happy to have found this tutorial video! Thank you! BUT I have a problem - I click on 'transform data' and then the 'double down arrows' and a window pops up saying 'we didn't recognize the format of your first file...' can you help me?
Awesome video, saves plenty of time, thank you! Is there a way that you know of to import multiple similar, but not exactly identical, pdf together? Like for example, they all have the same tables, but perhaps each has the same table in a different page\order, can you, is there a way filter the tables you want by header maybe?
Another way to do it is by making a function out of the transformation that is applied to one of the pdf, then apply the funcion to all the pdf, files. Then expand. In that way you do not have to worry about the sample data thing.
This Video Really Grt For me that I always stuck with pdf to excel conversion. This is a grt learning video for me .. but there a issue comes when I have customer pic in this pdf file with different format and I also want to download in excel then there is ? Comes always how do I can convert into excel with picture .. where I can solve my query to get it done in minimum time ? How can I send the pdf format to you so that u can saw the data in pdf format which i want to Convert in excel? Please reply
This is brilliant and it will save me so much time, it is coming back with an issue from me as I am using power automate to add PDF files to the folder but when I refresh my data it says "cannot access because another process is using it" - any idea what might be causing this
Great ! I am looking for a bit more complex case: multiple pdf's with multiple pages having the same format, data to be extracted for each page. Would you make a vid on this case, thank you.
Thats Really Great Information shared by you . really helps me . I have a Doubt with pdf file which is with images and i need to have this data into excel file with proper format . where i can send the sample pdf file to you so that i can have the Better way to resolve within minimum timeline.
Hi Leila Thanks so much for your video, i have a question, if the pdf has several pages and several tables, and want to just combine specific tables from different pages can this be done by power query? thanks so much.
Excelente Leila. Solo que a mi no me da con los estados de cuenta de Banco de America porque un solo estado de cuenta tiene varios cuadros con diferentes columnas. Puedes hacer un ejemplo con ese tipo de Estado de Cuenta? Gracias
Great explanation! However, I now need to import multiple PDF's wand they each have multiple tables. Power query only lets me select one of the tables in a given PDF file. Is there a way around this?
Get access to the complete Excel Power Query course here 👉 www.xelplus.com/course/excel-power-query/
Wow! You have a way of designing these examples and walking us through them that deserves a Teacher-of-the-Year award. Possibly also a Medal of Freedom. We all owe you a big thanks!
More than like *Teacher of the Millennium*. 😁😇🙏
🎉
Thank you, Leila! You're the best! After watching several other videos on this topic that were too complicated, too wordy or just "too-something", I discovered your video. What a breath of fresh air! Concise and successful! Can't ask for anything more than that. Your video helped me to accomplish exactly what I needed it to do. Again, thank you!
Tks Leila. I am now retired, but I used Power query a lot to transform PDF files on 500 transactions per day. Long live @ Power query
👍😁
Thank you SO SO much. People at work think it's a good idea to save their spreadsheets as MULTIPAGE PDFs and send them around the org instead of just sending the Excel file.... now I won't need to spend so much time making them an Excel file again.
Holy Moley! I am thinking about how much time this would have saved me on a project I did back in 2007. This is amazing! Thank you so much for sharing!
Same, i had to wite a vb sctipt that extarct invice data from some PDF's. It took me many months to do.
you telling me. i look at my work history of so much time wasted typing in data. arghh. well, at least i got time and a half once i hit 50 hours.
I'm glad I found this channel. I've saved MS Excel Playlist and planning to watch all videos very soon. High quality content is highly appreciated. I will support this channel. From India ❤
Thanks 🙌
Leila, you are THE MAGNIFICIENT! I appreciate you, and your channel so much!
Thank you so much!
This is awesome. I would normally combine the PDFs in Adobe Pro and then export them as an excel, but that's a one-time solution. Having the ability to refresh the file as more PDFs are added to the folder makes the process look much more streamlined.
At the 9:00 minute mark " ... let me know if you have ever imported files like this into Excel ...".
No ... not yet. However, I have been thinking of doing something similar with many years of historical data. My concern is that the pdf line items and headings may change from year to year.
Once again ... another intriguing lesson from Prof. Leila. Thank you!
It does have some limitations, but as long as there is a logic to the headers you can add PQ steps. You could for example add a mapping table for the headers or find a dynamic way to pinpoint the first row to import. If you do end up using this feature let me know how it went.
Thank you Leila Gharani, very interesting and knowledgeable videos of Excel are being shared by you. I always recommend your channel to my training participants in Afghanistan.
Thank you for your support!
This is awesome.. A big thanks for your time and efforts. For me this task was impossible until I watched this video. May you live long in good health and peace so that you can enlighten us with your latest contents in excel. Always love to spend time to watch your videos. Thanks once again.
U r awesome maam u saved my daily 3 hrs of time during shift. It was hectic for mr everyday to prepare excel data from 30-40 pdfs
Well explained, i rarely comment on videos, but you deserve it, thanks for sharing.
Your videos are definitely the best!!! Thanks for your contributions!
Glad you like them!
Leila I would like to suggest a feature I found on power query. It is the ability to anti-join two queries, in order to exclude items that exist in one of them from the other. This was brilliant for me cause I had a list with all my currently open orders and one list with the orders that got invoiced today and would get updated during the nightly system procedure. This would be simple if all entries in both lists were unique, but since the lists had orders with their products, they had the order number multiple times for each product. So appending the two lists and removing duplicates was a no no. Power query came to the rescue and not only it can exclude items that exist in one list from another, but now I can automate it. I can just provide the files with the entries and the managers, that need to watch the order fulfillment closely, have just to hit refresh.
Great! Thanks for sharing.
I need to try this. You know the countless hours I've lost over the years manually typing info from .pdfs to excel!
Oh yes. I can relate!
Nicely done, Leila! Excellent explanation of a complicated problem. Thank you for explaining this in such a straightforward way.
This is extremely helpful especially when looking at multiple invoices
Hi Leila, …I loved this demonstration, once again you taught me something I through I knew already. For you to have a smile: I was used for years doing this process by making my first file query, then advanced edit to change it into the function I will invoke against the filtered files query.
Then recently, I saw this help query. Saying myself, what’s that noise? I didn’t ask for, it’s weird It looks like something I need but didn’t know how to use it, so I trashed it all, and redo it my own old way of doing. …ridiculous, no?
As you already mentioned this tool is one of the best productivity and solid tool I have ever used for years in the area, tremendous, powerful, fit to a large range of users, from basic to advanced.
Thank you for the knowledge sharing
Very nice explanation , clear and profesional. Thank You!
Thanks!
Thank you!
Hello Leila! I am a great fan of your videos, very understandable and knowledgeable.
Happy to hear that!
This is therapeutic. I feel calm watching this video ❤️
Very helpful many thanks for your channel.
I hit a snag. The table did not translate all columns correctly. My work around was to use page then use functions like column type and text filters to get to my goal. A great useful channel and this feature will save me hours.
Thanks for sharing. Definitely handy, but a core assumption is that data needs to one level of rows and columns. If not, excel cannot handle multiple levels of rows and columns. For instance, and XML, which has n-depth, cannot be easily parsed by this built-in utility in excel. One needs to code it to handle multi levels
Many, many thanks Leila! Seen your video after a long time as i have lost my job and have no concern with Excel nowadays. Best of luck!
Wow! That was amazing. Thank you so much Leila! I'll be applying this today.
The first genious lady in the world.
Very easy to understand🌹🌻Maam I want to talk with you
You r one of my favourite Excel instructor..
Excellent informative and experienced training tutorials very very easy to understand Thanks
Super awesome as usual. Thanks a lot for sharing this with us all. it's really helpful
Excellent vid. The thing about filtering for the sample file was very good - not very obvious (to me, at least!) when you're looking at an error and thinking you've already filtered to get just pdfs!
Thanks!
Wow! I didn’t know this could be done. Thanks Leila!
With great power query comes great responsibility to watch your fantastic training video to learn its proper use. I like it when software is intuitive and I can figure things out but there is no way I can account for all the great features you point out.
Great to hear! Thank you.
Wow, you're amazing and think of major potential issues and resolve them. Amazing.
This video is fantastic! Thank you so much for creating it. I have a question, my original PDF files have multiple pages, hence, PowerQuery created multiple tables in the "Combine File" dialogue. How do you select multiple tables to be included in the sample file, as if I don't, the combined file will only have the first page of each file. Thank you!
I have the same issue
Great video...!!! I would like to know how to manage error when source folder is empty.
Amazing. That's very helpful. I ran a test with my PDF data, and it worked perfectly.
Thanks, it works if the file has one page. We need to follow the same step get rest of the pages
This really depends on the layout of the PDF.
True I actually have some structured and unstructured PDFs I’m able to extract the table I want for structured using python but I’m unable to find something for unstructured (basically the ones you can’t copy text from) does anyone have any solution also if someone wants the code for the python extractor lmk.
@@BangaloreUA-cam link
Great tutorial with showing possible mistakes and how solve.
Glad you liked it
OMG! Every reporting period I have to go through a client PDF report with multiple pages that is a complete mess. I didn't know that I could import the pdf file to Excel using Power Query so I can manipulate as needed. This is a huge time saver!! Thank you!!
Perfect!
Brilliant! Wonderful explanation, even better with the tips & tricks to avoid the usual pitfalls associated with the default actions.
Glad you enjoyed it, Mick!
after a long time got a video from you 🌹
Great video! I'm trying these with access files, its works perfectly.
I use this to help process test results for large fiber installations. Saves me hours of work. :D
Most mind-blowing thing learnt from this is excel file will be prioritised over pdf files as first file in PowerQuery.
I would be doomed and might be furstrated to start the whole project over again.😱
Very clear. Very beautiful teacher.
Very nice. I tried this and works very fine with single page pdf files or meaning excel extracts the information from one table in the page which one choses in the sample. I have been trying to bulk extract different types of table information from multiple page pdf files. Let us say in one page there are several tables in a multi page pdf and you have many pdf files which have the same table type inside. Is it possible to import all tables from all pages and from all pdf files at once? It would be very helpful if you do one video on with such examples 😉
This is really helpful! Thank you
You're so welcome!
very grateful to your tutor. Millions thanks for this.
It's awesome tricks. Save the time and work smoothly
Wow! Thanks so much for this video. I needed this and it's so helpful. Thanks for sharing
Thanks Ma'am.....this option save my lot of time.... 😍😍😍😍😍👍🏻👍🏻👍🏻👍🏻👍🏻👍🏻👍🏻
Thanks Leila, I like that you told us what Office versions you could use. I don't have 365 so I followed along and found I couldn't do it.
Same here
Yeah this works on really neat files in pdf.. How would you take this issue if there where multiple lines/strings of text in a single cell in excel?
Amazing! You just solved a major issue for us! Quick question... How do I filter out pdf files that don't contain "Table002" in the very beginning?
I am so happy until the last sentence that this feature is available only from 2021 and OF 360. Thank you so much anyway for sharing this excellent lecture!
Fantastic as always. Thanks Leila!
Excellent job Leila. Thanks for making our tasks super easy 🙂
i do this all of the time. very few if any of my colleagues know this. i convert pages of monthly brokerage investment statements into excel while everyone else is typing entry line by line.
Excellent and You saved my two days 🎉🎉🎉🎉❤
How you can find time to learn all that special features?!
Thanks you always save me !!
Hi Leila ... The video got me excited as I was struggling to find a way to convert PDF data into excel. I am using Excel 2016 but in power query i saw options were limited to Cvs , text & Excel itself but no PDF , consequently I could not convert the data. Is there i way around this problem ?
Hi! answers microft has a solution that worked for me: Power Query / Get & Transform -Get data from PDF missing
Your videos are awesome as usuall!!!
This is an amazing feature, and this video clearly demonstrates it. Awesome job. Thanks.
This is a wonderful feature I didn’t now about, thank you 🙏 ❤️
I was so happy to have found this tutorial video! Thank you! BUT I have a problem - I click on 'transform data' and then the 'double down arrows' and a window pops up saying 'we didn't recognize the format of your first file...' can you help me?
Thanks a lot Leila! You made my big problem go away!
i love you, Leila! You are the best!
amazing Leila, thanks for explain and teach us
My pleasure, Francisco!
Awesome video, saves plenty of time, thank you!
Is there a way that you know of to import multiple similar, but not exactly identical, pdf together? Like for example, they all have the same tables, but perhaps each has the same table in a different page\order, can you, is there a way filter the tables you want by header maybe?
How can i import choises input (from dropdown menu) in the pdf to excel?... btw... love your content...
Another way to do it is by making a function out of the transformation that is applied to one of the pdf, then apply the funcion to all the pdf, files. Then expand. In that way you do not have to worry about the sample data thing.
Can you please elaborate. Beacuse i have no Get Data option in my excel
Great video Leila! Just curious, why did you opt to use “ends with” instead of an exact match on the extension filtering?
This Video Really Grt For me that I always stuck with pdf to excel conversion. This is a grt learning video for me .. but there a issue comes when I have customer pic in this pdf file with different format and I also want to download in excel then there is ? Comes always how do I can convert into excel with picture .. where I can solve my query to get it done in minimum time ? How can I send the pdf format to you so that u can saw the data in pdf format which i want to Convert in excel? Please reply
This is exactly, what I was looking for. THANK YOU!!!
Mam your content are awesome .. could you please make video on price volume mix analysis for revenue and cogs?
This is brilliant and it will save me so much time, it is coming back with an issue from me as I am using power automate to add PDF files to the folder but when I refresh my data it says "cannot access because another process is using it" - any idea what might be causing this
Great ! I am looking for a bit more complex case: multiple pdf's with multiple pages having the same format, data to be extracted for each page. Would you make a vid on this case, thank you.
same issue here...
Thats Really Great Information shared by you . really helps me . I have a Doubt with pdf file which is with images and i need to have this data into excel file with proper format . where i can send the sample pdf file to you so that i can have the Better way to resolve within minimum timeline.
Hi
Thanks for sharing this.
any way to take the pdf file bookmarks to excel?
Hi Leila Thanks so much for your video, i have a question, if the pdf has several pages and several tables, and want to just combine specific tables from different pages can this be done by power query? thanks so much.
Really You Are Matchless ❤
I was the first who commented :-))). Another great lesson !
Thank you! Glad you like it.
A request to start sql or oracle queries command sessions
I like all your videos
Hi Leila, this video is great, But How can i import the files if each file has several tables? this only works if each file has only 1 Table.
Excelente Leila. Solo que a mi no me da con los estados de cuenta de Banco de America porque un solo estado de cuenta tiene varios cuadros con diferentes columnas. Puedes hacer un ejemplo con ese tipo de Estado de Cuenta? Gracias
This is mind blowing. Your videos are always so clear and thorough. Thank you!
Thank you this is great! Any advise on how to exclude the .ini files in the transform data section?
Disregard, you anticipated this question @5:07. Amazing!
Great! Thank you so much for sharing. Very useful!
Great video!
I agree it is like magic, and you explain its use to make it even better.
I would really like to know what too you use to create your tutorial videos?
Thanks Leila. Please, Must all files have the same structure (columns, rows, headers, etc)?
@LeilaGharani thanks for sharing, after you select "get data" and then "folder" what if the pdfs are not showing any ideas?
Great explanation! However, I now need to import multiple PDF's wand they each have multiple tables. Power query only lets me select one of the tables in a given PDF file. Is there a way around this?
I have never tried it like this I have tried however to convert from .pdf to xlsx with mixed success so this is very promising 🙂
Really useful! Thanks a lot Leila!
Thanks Leila! It’s a really nice feature.