Power Query: See it, Save it, Sort it - Part 4

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:

and extracted it into Power Query, in order to perform some transformations.

Last week, I appended Full_Dates to Sales_Transactions to give me a row for every consecutive date in Sales_Transactions:

This time, I will organise the data in Sales_Transactions. Currently, the appended Full_Dates rows are under the original Sales_Transactions data:

I start by sorting by Date. I can do this using the arrow dropdown on the right of the Date heading.

I choose to ‘Sort Ascending’:

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: 

In the dialog, I choose to merge the table with itself on Date, and choose a ‘Full Outer’ join:

This gives me a column of tables.

I continue by using the expand icon next to the heading on the Sorted Rows column:

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:

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!