Power Query: See it, Save it, Sort it - Part 2
25 May 2022
Welcome to our Power Query blog. This week, I continue looking at a sorting issue.
Last time, I started with some data for my imaginary salespeople:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/286/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
and extracted it into Power Query, in order to perform some transformations.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/286/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
I want to ensure that I have a row for every date, as I plan to apply time intelligence calculations to my data. To do this, I am going to create a list of all dates that I can append to my data. To find the range of dates I need, I start by right-clicking on Sales_Transactions in the Queries pane, and then I choose to make a Duplicate query. As the new query is copied from, but not linked to Sales_Transactions, any new dates will be picked up, but if I add steps to Sales_Transactions, they will not be picked up by the new query.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/286/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
I call my new query Full_Dates:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/286/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
Power Query has generated a ‘Changed Type’ step based on algorithms that sample the data.
= Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Amount", Int64.Type}, {"ID", Int64.Type}, {"Name", type text}})
Although Date does indeed contain dates, I want to use the data type ‘Whole Number’. I plan to create a list, and lists currently work with numbers, but not dates.
I could either change the M code is this step, or I can get Power Query to do it for me by using the dropdown under the data type icon:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/286/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
When I choose ‘Whole Number’ , Power Query recognises that this would be another ‘Changed Type’ step, so it offers to combine them:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/286/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
This is exactly what I want, so I choose to ‘Replace current’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/286/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
Date is now shown as a ‘Whole Number’. To find the maximum and minimum date, I can use the ‘Group By’ functionality which is on the Transform tab and the Home tab:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/286/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
This opens a dialog. I need to choose the ‘Advanced’ option, and I need to remove the grouping on date by clicking on the ellipsis (…) and deleting it.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/286/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
I add two [2] aggregations:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/286/image10.png/daf8c4f0259ce428269c0d3d4badd32b.jpg)
Clicking OK gives me the values I need to create a list of dates:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/286/image11.png/22c6daeb82d7d69ac88f878227e04b28.jpg)
Now I can enter the M code to create the list of dates; to do this, I add a ‘Custom Column’ from the ‘Add Column’ tab:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/286/image12.png/a1537847463e660a31158c8032525438.jpg)
In the dialog, I create a list from the columns:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/286/image13.png/917da985be13220165c8d2823e95344f.jpg)
The M code is:
= {[Min_Date]..[Max_Date]}
This gives me a column with a List in it, which will start from [Min Date] and end at [Max Date] and contain every number in between.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/286/image14.png/8c3be7af9f73d031acae69ed85a2e148.jpg)
I right-click on Custom and ‘Remove Other Columns’. I can then click on the expand icon to extract the List values:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/286/image15.png/e63c0a4c21afc9afb438aacc09a59317.jpg)
I can ‘Expand to New Rows’ as I want a column containing all the dates:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/286/image16.png/d082e3477129350b8a2a589156028e63.jpg)
I rename my column Date and change the data type to ‘Date’ using the dropdown from the data type icon. Full_Dates is now ready to use:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/286/image17.png/a468c9c28195e8f18fdedfa59a0cf042.jpg)
Next time, I will append this to my original query.
Come back next time for more ways to use Power Query!