Power Query: See it, Save it, Sort it - Part 5
15 June 2022
Welcome to our Power Query blog. This week, I amend my merge to filter my data.
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/289/pq-289-1.png/242e818360ee224063ac626ccd80f375.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/289/pq-289-2.png/875f8c2c5291cfbf299ef645a87d587d.jpg)
Last week, I chose to merge the table with itself on Date, and chose a ‘Full Outer’ join:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/289/pq-289-3.png/a727ec59580c90d42ac85f464f1aa16e.jpg)
This left me with a column of tables, which I expanded to create a Date.1 column.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/289/pq-289-4.png/2fd742899df4cbdee9542228d2e9b131.jpg)
This time, I go back to my ‘Merged Queries’ step:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/289/pq-289-5.png/4e2d8273501200e5db9ecb0021485744.jpg)
The M code for this step is:
= Table.NestedJoin(#"Sorted Rows", {"Date"}, #"Sorted Rows", {"Date"}, "Sorted Rows", JoinKind.FullOuter)
where ‘Sorted Rows’ is the name of the previous step. If I replace the second ‘Sorted Rows’ with ‘Changed Type’, this will give me Sales_Transactions before I appended the data:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/289/pq-289-6.png/57826a76b9822707f030d67fb879e283.jpg)
I amend the M code in the ‘Merged Queries’ step by editing the code in the Formula bar:
= Table.NestedJoin(#"Sorted Rows", {"Date"}, #"Changed Type", {"Date"}, "Sorted Rows", JoinKind.FullOuter)
When I enter this change, nothing seems to happen:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/289/pq-289-7.png/f5c685256a9642cedcc8019defa25454.jpg)
However, if I go to the ‘Expand Sorted Rows’ step, I can see that not all values in Date.1 are now populated:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/289/pq-289-8.png/3e7650b55d068fb186d96d4cc685d8cb.jpg)
The rows which have null in Date.1 are the appended data from Full_Dates that I need to keep, along with the original data from Sales_Transactions:
I can do this by creating a flag by adding a ‘Conditional Column’ from the ‘Add Column’ tab:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/289/pq-289-9.png/279d50bd1e42c5372157ab9e78f0c5a3.jpg)
This brings up a dialog, where I can enter the conditions for the row to be kept.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/289/pq-289-10.png/f406e802c4f82f7e54dec35e65f8b389.jpg)
I want to keep the rows where Amount is not null or Date.1 is null. This ensures I only keep the appended rows from Full_Dates if I don’t already have data for that day.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/289/pq-289-11.png/f90737c98e50b36e41e26f83bb3a5977.jpg)
Now I can filter on Keep Row and keep all rows where it is one [1].
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/289/pq-289-12.png/8aec2f4e432e5f4cc61bd40926a779be.jpg)
This gives me the data I need, and I right-click on Keep Row and select Remove.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/289/pq-289-13.png/bc94e5c13f1c43f69e40751c21c4b2d4.jpg)
Next time I will continue transforming my data.
Come back next time for more ways to use Power Query!