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!