Power Query: PDF Pandemonium – Part 6

Welcome to our Power Query blog. This week, I continue transforming some data that is coming in from a PDF file by working with last week’s function.

The tent business is doing well, and the UK division have plans to expand the workforce. I have a PDF file, and it contains tables for 10 stores. Last week, I created a function to apply to the Stores table.

In the Stores query, I need to make sure I only have the columns I need. To do this, I will remove the ‘Demote Headers’ step. I can reapply it before I invoke the function.

This means I have the store names in the headings to choose:

This gives me the store columns, and I can demote the headers again:

Stores is ready to use as the Source for fn_store. I go back to fn_store:

I need to provide a column from the Stores query.

I use Column1:

Invoking this query will give me a table:

I rename this table Expansion by Store.  I am going to use the M code already generated as a basis for this query.

I take the M code created for Column1, and replicate it for 10 columns.  I have renamed the steps to make it clear what I am planning.  I will rename the final ‘in’ statement when I have finished:

I have created a table for each store; now I just need to append them.  The M code function to append is Table.Combine({table1, table2,….}).  I add this line to the M code in the Advanced Editor.

I click ‘Done’ to see the results:

I have the results in the format I wanted for all stores. I can now ‘Close & Load’. I will choose ‘Close & Load to…’ so that I can load to ‘Connection Only’ to begin with to avoid loading all queries.

I can then right-click on the queries I want to load, and position them together:

I load the tables onto a worksheet:

I check the data types and the report is ready.

Come back next time for more ways to use Power Query!