Introduction
In this page, I am going to demonstrate how to use the Append function in Power Query and in a second page I am going to demonstrate what happens in Append when you do something wrong ... I will also discuss how to correct your error.
Append comes with Power Query and it allows you to join two or more Queries by putting one Query on top of another Query. Let’s have a look at an example.
The Example: Fortune 1000
I’ve got a lot of data from Fortune that I want to combine or consolidate. It’s data that comes from the Fortune 1000 lists from 2014 - 2019. In this example, I am demonstrating how to Append three tables, 2017, 2018 and 2019 and to keep it simple, we will just combine the first ten rows of data for each year. Combining all 1,000 rows for all years is essentially the same as doing ten rows for three years.
Keep each summary table separate from the other rows and convert each of them to an Excel Table … give each table a useful name.
My other two tables are called for_2018 and for_2017 and, most importantly, the column headers MUST be the same for every table we want to Append.
Create Your Queries: connection only
Now create a Query for each table: Data … From Table/Range … Close & Load To… Only Create Connection. There is no need to create a worksheet visible table at this stage since we are not going to use them except to create the final, Appended, table.
Then … having chosen to create a connection only, you will see the confirmation of that in the Queries & Connections panel, as below:
Repeat that for the other two tables until you have three Queries, all created as connections only.
Edit Your Queries to Append Them
Open the Query Editor now by double clicking on any of the Connection only items and open the Queries panel on the left of the screen:
Click on the for_2019 Query and then Append Queries … Append Queries As New:
You will now see the final step, during which you select Three or more tables in this case and then you will see that for_2019 has already been put into the Tables to append area since we chose to start with this one and finally we add the other two tables in the order we want:. Now click OK and it should have combined, Appended the tables:
If you started by clicking on for_2018 or for_2017 by mistake at this stage, you can move them up and down to put them in the right order … see the arrows on the right of the dialogue box?
Rename the Query
It will call your new Query Append1 … change that to for_2017_2019 in my case.
And there is your Appended Query that you just need to Close&Load to send it to a new worksheet …
That’s it! Four Queries, three of which are connection only and one final, Appended or combined table, on its own tab ready for use!
By the way, the beauty of Power Query: if you now copied and pasted the other 990 rows of data into each of your Excel Tables and refreshed the Append Query, it would give you all 3,000 rows of data as your combined Query table.
Download my Excel file so that you can follow along with me:
Duncan Williamson
17th March 2020
No comments:
Post a Comment