Power Query: Steps to Take – Part 4

Welcome to our Power Query blog.  This week, I continue with my worked example by unpivoting and combining my data.

The tent business has a new administrative assistant, who used to work in the United States.  George has provided some information, but it’s not yet in a format I can use:

In Part 1, I extracted the data and created a Base query, and took a Reference copy which I transformed to only hold the quantity rows: Quantities:

In Part 2, I created another query for the date information.   This query is called Dates:

Last week, I transformed the data so that the dates are in the correct format for my region.  

Now it’s time to put Quantities and Dates back together to get a list of dates and quantities.  I need to get the dates and quantities in columns rather than rows first.  To do this, I need to use the ‘Unpivot columns’ function.  In Quantities, I select the Month column and right-click. 

I choose to ‘Unpivot Other Columns’:

I need to perform the same unpivoting step in Dates:

I can now merge the data, using the Month and Attribute columns:

This brings my data back together:

I expand Dates to just give me the Value column:

Now I just need to tidy up.  I only need the Value and Date.Value columns, which I rename Salespeople Available and Week Commencing.  I also change the data type of Salespeople Available to Whole Number, and rename the query to Availability Q4. 

The data is ready to load.

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