Power Query: PDF Pandemonium – Part 6
20 October 2021
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/255/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/255/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
This means I have the store names in the headings to choose:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/255/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
This gives me the store columns, and I can demote the headers again:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/255/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
Stores is ready to use as the Source for fn_store. I go back to fn_store:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/255/missing5.png/5df9a796bc6a544983e0fc6deb1d280d.jpg)
I need to provide a column from the Stores query.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/255/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
I use Column1:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/255/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
Invoking this query will give me a table:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/255/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
I rename this table Expansion by Store. I am going to use the M code already generated as a basis for this query.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/255/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/255/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/255/image10.png/daf8c4f0259ce428269c0d3d4badd32b.jpg)
I click ‘Done’ to see the results:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/255/image11.png/22c6daeb82d7d69ac88f878227e04b28.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/255/image12.png/a1537847463e660a31158c8032525438.jpg)
I can then right-click on the queries I want to load, and position them together:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/255/image13.png/917da985be13220165c8d2823e95344f.jpg)
I load the tables onto a worksheet:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/255/image14.png/8c3be7af9f73d031acae69ed85a2e148.jpg)
I check the data types and the report is ready.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/255/image15.png/e63c0a4c21afc9afb438aacc09a59317.jpg)
Come back next time for more ways to use Power Query!