If you have Office/Microsoft 365 and don't see the PDF connector then you need to update Office. Find out how to get the latest updates here: support.microsoft.com/office/da36192c-58b9-4bc9-8d51-bb6eed468516
I'm running Version 2002 Office 365 ProPlus Build 12527.20988 and I am not seeing a PDF option under Get Data under Data. It does say that I am up to date as of 9/3/2020. Any additional thoughts?
I manually imported tons of tables from pdf files in my work in the last 2 weeks and my boss found that I was taking too long to do the task. This evening I started to think there must be a solution to do this tedious task automatically and quickly, so I found out that Microsoft Office 365 can do this. I feel stupid for not having known this before.
Thanks for the nice presentation, in case the excel navigator is showing pages on left and on Right hand side it shows page is empty? any Advise. Thanks
Not sure what you mean by navigator showing pages on left and right is empty. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Hi Amrish, I have a Power BI video here: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-BsXliHbOFDM.html and here: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Of2ML6TjkAI.html You might also be interested in my courses: www.myonlinetraininghub.com/power-bi-course and www.myonlinetraininghub.com/excel-power-query-course
Thanks, very much, I have this issue, I have different pdf files, these have different pages and tables, it may be possible to get the names of the queries so my query does not fail, since the files differ in pages and tables and my query fails
In my PDF, I have 14 columns and 47 rows. I want to show each row in a single cell of power query so that the trailing spaces are not lost. Pls advise.
Thank you so much, the technique will help me so much. I did not know Power Query would be able to identify pages with tables and separate them from pages with text. super-amazing👏👏
Madam, I am re-framing my question I have Multiple PDF file in one folder, in some PDF when I see in Power Query it shows as Table 3 and for some PDF it shows the same content in Table 4. so importing becomes problem if I select the sample from table 3 then only data from all pdf from table 3 will be considered for processing. one common thing I have is table has one column Name as " LineNO". First I should find if PDF has a table having the column as LineNO if Yes then consider that as the table to process and move to next PDF and do the same process So Importing of Table having Column Name as "LineNo" is the condition that to be passed before Transform.
What If you don’t have Excel 365? I have excel 2016. What would be the best solution in this case? I’ve tried several attempts starting from PDF but the results don’t always work.
Hello, I'm not good with Computer so this is a bit hard to understand for me... Let say I have multiple pdf files ( or I can combine multiple pdf files into 1 big file, separate by each page) which have the same format. Now I want to extract some specific data from said files using the "Get data -> From PDF" function. The problem now is I'm having multiple pages extracted and don't know how to apply the same configuration to each page and then combine them into 1 table... Can someone help me with this problem? Thank you so much in advance.
You can specify the tables you want to import from the PDF, or import them separately and then append the tables. If you get stuck, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Hi Shakthi, sounds like you need to update your version of Office/Microsoft 365: support.microsoft.com/en-us/office/when-do-i-get-the-newest-features-for-microsoft-365-da36192c-58b9-4bc9-8d51-bb6eed468516?ui=en-US&rs=en-US&ad=US
So I've set up a Power Query from a PDF, which involved quite a lot of tidying! We have an old system that provides messy PDF outputs for data, and I'd like to add new PDFs and get Power Query to do the same applied steps every time, so I can feed it new PDFs and get clean outputted data as separate tables. Is this possible?
I'm using this method to import data , but when i'm trying to refresh using a diff pdf having similar data , i'm getting errors as Power Query picks up diff tables than i want , kindly help. I liked and subbed by the way , excellent video.
Power Query hard keys parameters based on the original PDF you import. If your new PDF has different tables and column headers, then you need to edit the M code accordingly.
Check here for where to find Power Query in your version of Excel: support.microsoft.com/en-us/office/where-is-get-transform-power-query-e9332067-8e49-46fc-97ff-f2e1bfa0cb16
My PDF is a scan of a table and the Power Query just says "no data in table". Thinking the quality of the scan isn't good enough for the software to recognize. Any way around this?
Hi Carrie, Power Query can't read images in PDFs. You can try using Excel's new import data from a picture: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-f94JJLVbZhU.html Or if you don't have that version of Excel, you can try the free mobile app: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-nfMv_xvMS7E.html
Thanks, Myanda, this is an excellent video. I completed your Power Query Course in 2016. I am now fully retired but have maintained an interest in EXCEL and each week I receive a PDF file from a data provider that I need to put into a data table for my SMSF. I developed a VBA program to analyse the data once I have placed the data in a Table. I will try and use Power Query to do this step as it could be a time saver. Thanks again. The course was excellent and I would recommend it as it is the future direction being taken by Microsoft.
My pleasure, Peter. Sounds like the data is formatted as text and not a number. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Please, i have a question. After we import the PDF file via Power bi, how can i select all tables at the same time. when i importe a long PDF file, i get a hundreds tables that i cannot select one by one. that may takes a life time. thank you.
Is there a way to the import data and select all tables automatically? For example, you only have to specify the location with multiple PDFs and the automation does the loading + selecting all tables
Hi Sean, I haven't tried multiple tables from multiple PDFs, but I know you can get multiple PDFs that all have the same structure with one table from a folder. You'd have to test it for multiple tables, but I would have thought so.
It's a HD video. I suspect your default playback settings were set to a lower quality. Please check the cog icon in the bottom right of the video to alter the settings.
I have multiple excel in some excel data is available in table 3 and in some excel same data is available in table 4 . So how to transform both table? Pl support me
Scanned PDFs aren't true PDFs, they're images. You can use Import Data From a Picture instead: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-nfMv_xvMS7E.html
If you don't see it then you'll need to update Office. You may be on the semi-annual channel which only gets updates once per year. If so, you'll need to speak to your IT people. More on the different update channels here: docs.microsoft.com/en-us/deployoffice/overview-update-channels
Hi, I just got a recent update Nov 2021 of O365 & sadly it seems that the option for getting data from PDF is no longer available. Do you know a workaround?
@@MyOnlineTrainingHub I have Excel 2019 at home & the latest version of O365 Excel at work both have been updated within November 2021. Strangely both versions look the same when you go to the get data [option wise]. I hope Microsoft haven't withdrawn this PDF option. Thanks for the reply.
If you have a pdf that is always in the same format, is there a way to create a template so when you create an import button you don't have to do all the work every time? Is this too much for a macro to do?
hi, great video! question for you MyOnlineTrainingHub - I have several tables from PDF, they are basically one long table (22 tables in import) but only the first table has headers. Excel doesnt recognize or understand that columns from each table are the same so it ends up staggering them over. so if table 1 ends on column 10, then table 2 starts on column 11, table 3 on column 21, and so on. how do you make excel recognize or understand that you want it to simply stack all the tables on top of each other and recognize headers from first table you pick?
I'm not aware of any limits to the number of pages so in theory it should work. Maybe test it on a smaller PDF containing the same data to see if it's the data that's the problem rather than the number of pages.
The page range [PageStart=7, PageEnd=8] must be exactly in that format (with parenthesis, camel case followed by comma)? Is PageStart and PageEnd variables or built-in within PQ? Can I use something like [Pages=7-8]?
I'm a mailer and sometimes get address lists in pdf. I'm going to try this at work. Probably going to take a few tries but if I can get it to work I'll look like a stud. We usually have to have the client send us a xls or csv to get their addresses imported to our addressing machine.
HI Mynda, I'm your silent follower and really like what you do. Power query is not been able to retrieve data from password protected PDF file. It is not getting forward; can you help me out please
Hi Mynda, how can I extract words one by one without spaces for a specific text from cell to others? please, i found a solution but it's complicated, sure you have something better
Hi Reda, I'm not sure what you mean. Please post your Excel question and sample file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
hi there... great video... But is the option of get data rom pdf available only in O365 or is it possible in older versions too.. pls advice how to do it... thanks
Nice tutorial!! If You Need some Quick solution without a connection with the PDF file, You can import the PDF file in Word and there there are all the tables that You Need! After that You only Need to Copy and paste on Excel and edit what You want!!
Hello. I'm wondering if Power Query can be used to import and process larger lists in pdf? F.ex. General Ledger or similar. Sometimes we get longer lists in that format and it would have been very useful to be able to edit them before they are taken over in excel. I have tried but unfortunately without success.
to be honest, its a challenge to any tutor on this subject as ms excel developer team is deliberately silly by not adopting the method in pivot table field selection means: click & drop....the management of ms gets a gut to accept such a dummy operation & take to market as ms product in 201x~2020.....that gives bill gate omg.............
Awesome. I've been exporting my PDFs to text and the connecting to Power Query. This should be better. I don't see this update on my work account. Is this only for personal accounts?
Thank you Mynda for this valuable video. This really helps when you only a receive pdf file and prevents time zapping manual work. Your email tips & tricks and video demos are awesome!
This is an interesting video for me, PDFs and Excel have been a nightmare in the past. Power Query is the answer, and it is a feature I’ll need to get a lot more comfortable with, thanks for the presentation Mynda, really good.
Thanks, Mynda! Quick question. Are we able to specify a conditional last page i.e. [StartPage = 3, If(condition, EndPage = 7, EndPage = 8] to cater for variable number of tables.
Hi, I use Excel on a Mac, do you know the nuances regarding Excel PC vs Mac? Like the shortcuts? Thank you (your channel is super instructive and easy to follow)