Power Query: Steps to Take – Part 4
5 October 2022
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image1-1662617768.png/5b7a5b15019781243cdc6341ad8bec7b.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image2-1662617778.png/a78462c841b95ff3abd371ec9006c1d1.jpg)
In Part 2, I created another query for the date information. This query is called Dates:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image3-1662617790.png/8c41e1ea96c96c035ebe8b954a091e9a.jpg)
Last week, I transformed the data so that the dates are in the correct format for my region.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image4-1662617798.png/4e3a9afbbba1171bafa287705cd938de.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image5-1662617812.png/367a555cc4f7341c4b4af06806d2aaf0.jpg)
I choose to ‘Unpivot Other Columns’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image6-1662617823.png/4b009ab4b436ef6ffe8eaf5e7ee95660.jpg)
I need to perform the same unpivoting step in Dates:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image7-1662617841.png/5ff18572fc395a205a75a84786dd2c2a.jpg)
I can now merge the data, using the Month and Attribute columns:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image8-1662617879.png/fee0bef6af002a0541bc147c5955928a.jpg)
This brings my data back together:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image9-1662617897.png/cd408dbb245fbbc90ca9fe5700221cb2.jpg)
I expand Dates to just give me the Value column:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image10-1662617905.png/ce4c7880c547c25d26410204dc3aab19.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image11-1662617917.png/a9954db624d0d52573060f4231aa672e.jpg)
The data is ready to load.
Come back next time for more ways to use Power Query!