Power Query: PDF Pandemonium – Part 4
6 October 2021
Welcome to our Power Query blog. This week, I continue transforming some data that is coming in from a PDF file, by creating transformations in preparation for developing a function next week.
The tent business is continuing to do well, and the UK division still plans to expand the workforce. I have a PDF file, and it contains tables for 10 stores. Last week, I transformed Pay Scales and this week I turn to the Stores table.
I have 10 stores in my table, and I need to perform the same transformations for each one. The first step I will take is to merge the columns I need for Store 1. I select Store 1, Pay Scales and Workforce expansion whilst holding down the CTRL key. When I right-click, I have the option to ‘Merge Columns’.
Clicking on this option reveals a dialog:
I choose not to use a separator, since I can split by non-numeric and numeric characters later. I want to call my new column Store 1, but Power Query won’t let me do this as this is one of the names of the original columns, so for now I take the default Merged.
I can adjust the step created from:
Table.CombineColumns(Table.TransformColumnTypes(Source, {{"Store 1", type text}}, "en-AU"),{"Store 1", "Pay Scales", "Workforce#(lf)expansion"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged")
to:
Table.CombineColumns(Table.TransformColumnTypes(Source, {{"Store 1", type text}}, "en-AU"),{"Store 1", "Pay Scales", "Workforce#(lf)expansion"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Store 1")
This is then accepted with no issues:
I repeat this for the other stores.
I could keep working on all the stores together, but it would soon become a large table. Instead, I am going to work on the stores separately. Before I do this, I need to make sure that the store name is included in the data, so I demote headers using the option from the Transform tab:
My data is now safely stored in the rows, and I can create a query from a column. I right-click with Column1 selected:
This creates a new List Query.
I will rename my List Query to Store 1 to reflect the data.
What I want now, is to create a series of steps that will work on any of the stores. I need a function. I start by taking a reference copy of Store 1.
I call my new query fn_store.
The first step is to convert this list to a table. I can do this from the List tab or I may otherwise right-click.
A dialog appears where I shall accept the defaults:
This gives me access to the other tabs.
I start by promoting the first row to headers using the option on the Transform Tab.
I am ready to enter the transformations required and convert this query into a function next time.
Come back next time for more ways to use Power Query!
