Power Query: See it, Save it, Sort it - Part 4
8 June 2022
Welcome to our Power Query blog. This week, I reorganise the data from my appended queries.
In Power Query: See it, Save it, Sort it – Part 1, I started with some data for my imaginary salespeople:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/288/pq-288-1.png/24c519e3fdbd4f9ed24bd9d63feababf.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/288/pq-288-2.png/6cad36de1a05198ed5c43a1a2d9c044a.jpg)
Last week, I appended Full_Dates to Sales_Transactions to give me a row for every consecutive date in Sales_Transactions:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/288/pq-288-3.png/614203f89bf5feff40dc26d304fb1ba7.jpg)
This time, I will organise the data in Sales_Transactions. Currently, the appended Full_Dates rows are under the original Sales_Transactions data:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/288/pq-288-4.png/086d54e11df0bf9960fa398dc588cc42.jpg)
I start by sorting by Date. I can do this using the arrow dropdown on the right of the Date heading.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/288/pq-288-5.png/233b9f18481e9facc012819e39dd580f.jpg)
I choose to ‘Sort Ascending’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/288/pq-288-6.png/82605a9b9cd5c8f920a95821be77566d.jpg)
The Full_Dates query had no Amount, ID or Name columns, so these have been left as null values. I only need these rows if there are no transactions from the original data on that date, so now I need a way of removing the extra null lines.
There are several ways I can achieve this, and the first is by using a merge. I can check the data against the original Sales_Transactions data.
I start by generating the M code I will need. On the Home tab, I choose ‘Merge Queries’ from the Merge Queries’ dropdown:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/288/pq-288-7.png/71bacb3ccc1da650dfab288f9aa5584f.jpg)
In the dialog, I choose to merge the table with itself on Date, and choose a ‘Full Outer’ join:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/288/pq-288-8.png/38aaa3b1a6227487d5bbbcf2d06cbb4b.jpg)
This gives me a column of tables.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/288/pq-288-9.png/764b02d55f4a713add080ffd0c65fd78.jpg)
I continue by using the expand icon next to the heading on the Sorted Rows column:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/288/pq-288-10.png/dfb6811e43463a354f017c100116b7cf.jpg)
I have only selected Date, and I have chosen not to ‘Use original column name as prefix’, therefore my new column will be called Date.1:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/288/pq-288-11.png/c75ff3d9e130795cf2a72167aae4b38b.jpg)
Next time, I will show how I am going to use this to filter my data.
Come back next time for more ways to use Power Query!