Renaming and Appending Queries
Renaming and Appending Queries
Previous Session Summary
Lesson Synopsis
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 Video
Resource listing
Video Contents
- 1:33 - What is the the Queries and Connections pane?
- 01:50 - How can I bring up the Queries and Connections pane?
- 02:04 - How can I rename a query?
- 03:20 - How can I append one query underneath another?
- 03:50 - What's the difference between Append Queries and Append Queries as New
Lesson Details
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.
The Summary - in this lesson we covered...
How to bring up the Queries and Connections pane
How to Append Queries
- The Queries and Connections Pane shows information about Queries in the current workbook including the amount of rows in each query. You are able to rename queries as well as perform other actions on each queri through this pane.
- Go to the tab Data
- Go to the section Queries and Connections
- Select the button Queries and Connections
- This command puts the results of one query directly underneath another either as a new query (Append Queries as New) or in the selected query (Append Queries)
- Go to the Home tab
- Locate the Combine section of the ribbon
- Select the Append Queries drop down and select the required option