How to Import PDF Files into Excel with Power Query

hey there it's John from Excel campus and in this video I'm going to explain how to import a PDF file into Excel with power query so this is an awesome new feature of power query and we're going to use at this PDF file here so this is a report that's exported from a service called GT metrics it's a free service it analyzes web page speed and I ran that on one of the Excel campus web pages and as you can see it's a pretty detailed report has a lot of tables here different styles of tables as we scroll down it even has some charts in it here I'll continue to scroll it has this waterfall chart with a lot of data a lot of rows of data below that spanning over two pages have some additional charts here and and different data points these thing I'm interested in is this report at the bottom on pages 8 & 9 so here we have this PageSpeed recommendations report and as you can see here it's a table relatively structured table of data but it does expand across two pages here so both pages 8 & 9 have this table so we're gonna look at how to bring this into Excel with power query so we'll jump back over to excel we'll go to the data tab on the ribbon and the get data menu from file and you'll see this new option from PDF so we'll go ahead and click that that'll bring up the import data window and we'll navigate to where our PDF file is stored go ahead and select it and click import and that'll take a few seconds to load up this navigator window here with a preview of the data that a power query has found in the PDF so there's a few different ways it groups the data as well at the top here we have tables within each page and you can kind of click through these and see the different data or the tables that it's found within each page one here table 11 on page 3 shows that long waterfall table the other thing it does scroll down to the bottom it also shows entire pages within the PDF so you can click these here and that's just going to show all the data it found within the PDF now this is not going to be as structured it's going to be in different columns depending on what data is included in the PDF so you might want to use this or you might want to use tables in my case I want to just bring in those two tables from pages 8 & 9 so if we go here table 24 is what I want for page 8 and then I believe it was table 27 for page 9 so I'm going to hit this check box that says select multiple items and I'll select table 24 again check that box and then 27 as well then we're going to hit transform data and that'll take a few seconds to load up the power query editor and here you can see we have both of our queries on the left side in the queries paint this is collapsed you might need to expand the queries pane and here is our data and we want to combine this data we're going to use an append for that I have another video on the append function and power query I'll put a link to that in the description below this video but before we append these we might want to do just a little bit of cleanup work and that cleanup work is that within each of these queries the header row is actually in row 1 here the header row that describes the data below is in row 1 and we can click this button here and choose use first row as headers and that will promote that row 1 up into the header row so we want to do that for both of these tables here we'll go ahead and hit use first row for take the second tables well on page 9 and that's really all the cleanup work we need to do for this specific example you might also want to do some filtering if needed combine columns things like that but in this specific example it's pretty straightforward so next we're going to append these two tables and on the Home tab of the ribbon here there is an append queries button we're going to hit the drop down here and choose append queries as new so this is going to create a new query with based on these two queries the append window will come up and for this we just have two tables but if you had three or more tables you can still do the append as well so the first table will be Table eight or the one for page eight and we'll also choose a table for age 9 go ahead and hit ok and that's going to create a new query here called append of course you can rename this over here in the query settings pane that's appended or stacked both of those queries together so now really all of our work is done we've brought in the data from the PDF and we've appended these queries together now at this point we can close them those that load the data I'm going to choose close and load 2 and I'm just going to create connections only first otherwise if I hit the top half of that split button power query would output tables for all of the queries and I don't need that so I'm going to choose only create connection and hit OK that'll open the queries and connections pane over here and show my queries they've all just have connections created and from my appended table I do want to output that to a table in Excel so I'm just going to right click here choose load two and then now I can choose table we can put that in a new worksheet or the existing worksheet click OK and then that's going to create a new table on a new sheet in this workbook with the data it took a few seconds to load up but there it is so here again is my appended data we can see we have 45 rows loaded across multiple sheets from a PDF file so this is pretty cool powered query did a pretty good job to read this PDF file again a pretty complex PDF file here with a lot going on inside of it power query is able to pick up all these different tables and data points and then allow us to use the power and the magic of power query to clean up that data as well so I'm curious to know if you have examples of PDF files that you would like to import into Excel and also clean up with power query so we'll put a link below this video in the description below this video where you can upload a sample file and we'll pick a few of those and then I'll create videos that walk through how to import that data into power query as well so if you have a complex example feel free to load it up there and we'd love to create a tutorial on this fantastic new feature of importing PDF files into Excel and automating the process as I've explained in my previous videos on power query not only are we creating and cleaning up this data that we're importing we're also automating this process so if the PDF file or the data changes in the future we might just be able to click write right click refresh this table here and just run that query again and output the new data so great new feature again leave a comment below with any questions or suggestions thanks again for watching have a great day and I'll see you in the next video [Applause]

Channel: Excel Campus - Jon

Views: 142,997

Keywords: excel, excel campus, power query tutorial, power query in excel, excel for beginners, excel data from pdf, pdf to excel table, excel data from pdf form, excel basics for beginners, import pdf into excel free, import pdf into excel spreadsheet, import table from pdf into excel, import scanned pdf into excel, import adobe pdf into excel, import multiple page pdf into excel, import multiple pdf into excel, how to convert pdf to excel spreadsheet

Id: C6vqy30PDnE

Length: 7min 28sec (448 seconds)

Published: Thu Jun 18 2020