1. Merging and appending queries in Power Query
As mentioned in the previous video, it is quite common that your data is spread across several different sources, which you will need to connect together in order to analyze your data properly.
This process of stitching together the data is mainly accomplished with two features in power query: append & merge.
Let’s take these two tables for example:
Here we have the daily sales for two months: January and February. In an ideal situation, both these tables would be joined together in one dataset by stacking them on top of each other. In power query, this is called “appending” the queries together. Something very important you should make sure of before appending is that the column names should match exactly between the tables you are appending, otherwise you will end up with null values. Since both these columns have the same name in both tables, we are good to go.
We select the January query, then click on Append here:
Then we select the February table to append to this one.
Now we can see that our query contains data for both January and February.
This process can be applied to append any number of tables, and will come in handy when dealing with Excel files with multiple sheets that are all in similar formats. Just remember to pay close attention to your column names.
The next transformation we will learn about is the merge queries feature.
Let’s check these two example tables:
One table contains Date, Country ID, and units sold, while the other column contains Country ID, and the name and capital of each country corresponding to that country ID.
The tables have the country ID column in common, so that is the column that will be used to join the two queries. Do note that the column names don’t need to be the same in both tables, it is the data inside the columns that is compared.
We’ll select the country ID column from the Sales table, then click Merge queries:
In the dialog box that shows up, we will want to select the second table we are interested in merging with the Sales table:
Then we select the Country ID column from both tables.
Once we’ve done that, Power query tells us how many matches there are between the two tables. Since there are only 3 matching country ID’s, one of the rows is expected to have a null value when we use the left join kind.
This output looks a little strange, but basically what has happened is that the entire second table has been converted into a column, which you can then expand by clicking the diverging arrows on the right of the column name.
We’ll select “Country Name” and “Capital City” and unselect “use original column name as prefix.
As expected, there is one null value in the table.
Let’s see what happens when we change the join type to right outer:
Now power query tells us that all 3 rows were matched from the right table.
The row with the null value was essentially filtered out, since it doesn’t exist in the right table, which we are taking as our base table when we do the right join operation.
Other join types will have different applications depending on your needs, so we encourage you to experiment with them and see how they work.
Let’s see how to combine data from different tables in the Northwind traders database to enable us to analyze sales more effectively.
2. Let's practice!