Please note javascript is required for full website functionality.
MVP

Blog

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!

 

Newsletter