Please note javascript is required for full website functionality.


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

1 June 2022

Welcome to our Power Query blog.  This week, I append queries to extend my data.

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 looked at the earliest and latest values of Date, and I created a query that would have a row for every consecutive day between those values.  I called that query Full_Dates.  

Now, I want to append Full_Dates to Sales_Transactions, so that I will have a row on Sales_Transactions for every consecutive date. 

I start in the Sales_Transactions query and choose ‘Append Queries’ from the ‘Append Queries’ dropdown on the Home tab.

In the dialog, I use the basic ‘Two tables’ option and choose Full_Dates.  

This extends the Sales_Transactions table:  

Note that the Date column is now type ‘Any’.  This is because I have appended a column with data type ‘Date’ to a column with data type ‘Date/Time’.  

I could rectify this by using the dropdown from the data type icon to change the data type of Date to ‘Date’.  

However, I can solve this a different way.  I change the data type of the column before the ‘Appended Query’ step:

Power Query checks that I want to insert a step:  

I continue, and I am prompted to amend the existing ‘Changed Type’ step.  

I choose to ‘Replace current’ and the step is amended:  

This means that the data type on Date of Sales_Transactions matches the Date on Full_Dates for the ‘Appended Query’ step:

This saves me adding a new step to change the data type of Date.

Next time, I will organise the data in Sales_Transactions.

Come back next time for more ways to use Power Query!