Power Query: PDF Pandemonium – Part 3
29 September 2021
Welcome to our Power Query blog. This week, I continue transforming some data that is coming in from a PDF file.
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 two Reference Queries, and this week I will continue transforming Pay Scales.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/252/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
I start by keeping the columns I will be needing for this table.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/252/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
This means I can concentrate on the data I need to transform for this table.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/252/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
Having checked the data, I actually only need the pay scale columns, so I select them whilst holding down the CRTL key and click on ‘Remove Other Columns’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/252/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
Power Query incorporates this into the existing ‘Remove Other Columns’ step.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/252/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
I can remove empty rows from the Home tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/252/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
I can now look at how to transform my data from this into a useful table.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/252/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
I want to transpose the data, but if I do this with some of the data I need in the column headings, I will lose it. First, I need to demote the column headings so that I have the information in a row. I can do this from the Home tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/252/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
This creates a ‘Change Type’ step which I delete as I am not ready to decide column types yet. I am now ready to transpose my data, using the option on the Transform tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/252/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
My data is starting to take shape.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/252/image10.png/daf8c4f0259ce428269c0d3d4badd32b.jpg)
I can rename the headings.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/252/image11.png/22c6daeb82d7d69ac88f878227e04b28.jpg)
I want to show a start and end salary, rather than have the information in one column. I can split the Salary column from the Transform tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/252/image12.png/a1537847463e660a31158c8032525438.jpg)
From the dropdown, I choose to split ‘By Delimiter’; this brings up a dialog.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/252/image13.png/917da985be13220165c8d2823e95344f.jpg)
I choose to split by space at each occurrence of a space, because this will give me a column with the lower and upper limit.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/252/image14.png/8c3be7af9f73d031acae69ed85a2e148.jpg)
I delete the automated ‘Changed Type’ step again. I don’t need Salary.2, so I can remove this. As usual, I do this by selecting the columns I want to keep and ‘Remove Other Columns’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/252/image15.png/e63c0a4c21afc9afb438aacc09a59317.jpg)
I want Salary.1 and Salary.2 to be numeric columns, so I need to remove the £ signs. I can do this by selecting the columns and replacing £ with blank. I start by using ‘Replace Values’ on the Transform tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/252/image16.png/d082e3477129350b8a2a589156028e63.jpg)
This provides a dialog where I can enter the details.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/252/image17.png/a468c9c28195e8f18fdedfa59a0cf042.jpg)
This will remove the £ signs.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/252/image18.png/61935f470d8b9f21b7b7683ffca40895.jpg)
Next, I change both columns to whole numbers. I can do this from the Home tab or the Transform tab, or by using the right-click menu, and changing the data type.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/252/image19.png/06909e3fbfc8faf87de7555c0b99e9d3.jpg)
I get an error, but I can use ‘Replace Values’ again; this time, I choose ‘Replace Errors’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/252/image20.png/173ef895b62ce391674d271b99c4d178.jpg)
I want to replace it with null, not zero [0], since zero is the starting point for ‘Pay Scale A’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/252/image21.png/bc31e9359fd69b42bc104de457d8f9f8.jpg)
Next, I need to transform the Percentage Increase column. If I make it the data type Percentage, I will get values of over 100: I need to divide the values by 100 first, which I can do from the Transform tab, but first I change the data type to ‘Decimal Number’. This will allow me to access the ‘Standard’ dropdown.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/252/image22.png/059b7a0f2b398df09f943b811a5b3126.jpg)
I choose ‘Divide’ and enter 100 in the dialog.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/252/image23.png/287595eacf557e9bf11ea5283a715ce1.jpg)
I can now set the correct data types for all of the columns.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/252/image24.png/c20c20624705a0da5ec0bb162b062eab.jpg)
I rename the salary columns, and my table is ready to ‘Close & Load’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/252/image25.png/d1f447eaaef4f12aad7eba0759ff10c1.jpg)
Next time, I will look at the Stores query.
Come back next time for more ways to use Power Query!