In the last two lessons we’ve had two datasets, one showing POs run in January and one showing POs run in February. We’ve done some really minor cleaning and transforming and now we have each dataset ready for us in separate tabs
It would be a shame to have to copy and paste the February POs query table underneath the January POs table query though, so in this lesson we’re going to look at how to use the Append function which puts the results of one query directly underneath another. No more copying and pasting required!
The Append Function
The Append function takes the results of one query (or more) and puts it directly underneath the results of another query – a sort of copy and paste. Here are the nuts and bolts:
The Append function uses a Primary query (the query that we’ve selected) when choosing to Append and then subsequent Secondary queries. The Append function identifies the column titles in the Primary Query and then goes into the Secondary queries to find the same columns. Where it finds the same column titles then it will put the information in the columns in the secondary queries directly underneath the columns in the Primary query.
However, if PowerQuery finds columns in the Secondary queries which are not in the Primary query, then it will simply add those columns onto the end of the Primary query. For the Primary queries’ rows, for those new columns, there will simply be no data.
This is a good point to note that PowerQuery is case and character sensitive pretty much everywhere. What this means is that a column called ‘Date Ordered’ in the Primary query and ‘Date ordered’ in the Secondary query will not go one underneath another when appended. This is because, as far as PowerQuery is concerned, they are called totally different things and are not the same column. ‘Date Ordered’ in one query and ‘DateOrdered’ in another query will have the same effect and the columns will not go one underneath the other. Only ‘Date Ordered’ and ‘Date Ordered’ will be appended correctly.
Next – Append Queries vs Append Queries as New.
Simply Append Queries as New creates a new query, where the tables for appending are appended together. The original queries remain. Append Queries is different. In Append Queries, we nominate a Primary query, one that already exists, and then the information from the Secondary queries are added into that Primary queries. No new queries are created.