Office and microsoft 365 users can now import data from a pdf to excel using power query it's the same functionality that's been available in power bi desktop for a while now it's super handy for importing data tables inside pdf documents because power query can locate them for you ready to import and if the data is a little messy we can use the power query transform tools to clean it before loading it into excel or power bi let's take a look this is the pdf i'll use for this example you can see it's mostly text and a few charts and then on page seven here we have a table and again on page eight another table and they're the two tables i'm going to import into excel let's pop over into excel i've got an empty workbook here so on the data tab of the ribbon we're going to get data from file and then from pdf here i need to navigate to the pdf that i've saved on my pc it's this one so i'll click the import button it's going to open the navigator window on the left we have the list of tables that it's found and all of the pages i can click on the tables and get a preview in the navigator pane and this is the second table with the various columns of data we can also import the pages themselves and then parse that data i'm going to check the box to select multiple items and i want the two tables now if they're in the perfect layout ready to load to your worksheet or into the powerpivot model you can click load and it will deposit the data there i'm going to click transform data to open the power query editor window because my data is a little messy we need to do a bit of work before we load the data into excel the first table here doesn't need too much tidying up the first column is just an index number which i don't need so i'm going to press the delete key and get rid of that the next thing i notice is that it's not in the perfect tabular format which i need for working in excel to be a tabular layout it should have one column for the number of simulations one column for the model and one column for the cmip number so let's quickly fix it i'm going to start by duplicating the query so right click duplicate the first query is going to contain my cmip3 data and the second query is going to contain the cmip5 data so let's start with the first query this is simulation data so i'll call this one simulations three and therefore i want to remove the cmip5 data so selecting those two columns just hold down shift or control to select them both and then press the delete key now i.