Power Query: Project Population – Part 8

Welcome to our Power Query blog.  This week, I combine my queries from a public data source.

I have found some information on population growth provided by The World Bank, which I am using as an example of how to transform real-life data. 

I have been transforming the data, and last week, I completed transforming the Country-Series query.  This query gives me information about the source of the population data in the Country query:

Now I am happy with the Country-Series query, I will link to this information from the Country query.

There are several ways I can achieve this.  Moving to the Country query, I look at the options available in the ‘Combine Queries’ section of the Home tab:

There are two ways to join queries from this tab.  I can ‘Merge Queries’ or ‘Append Queries’.  Appending queries joins queries by adding the second query to the end of the first.  We append queries when they share most of their columns.  If I append the following tables:

I will get one longer table:

 If however, I want to combine the data from another table with only one shared column:

Then appending these tables would give me this:

Merging by linking them on Fruit instead, I would get this:

For the data I am looking at, Country and Country-Series share one [1] column; Country Code, and provide different information about the same country values.  Therefore I want to ‘Merge Queries’.   

I choose to ‘Merge Queries as New’.  The ‘Merge Queries’ option would replace Country with the merged query and I want to keep my separate queries too.  Choosing this option presents me with a dialog:

I choose the Country-Series query.  I am not allowed to merge until I select an equal number of columns from each query:

In this case, it is obviously the country that I need to merge on.  Since I am merging, it doesn’t matter that the name of the selected column in Country-Series does not match; it is the data in the column that needs to be of the same data type.  If I had been appending instead, then the different names would have indicated that the columns should appear separately in the combined query.

There are different values that I can choose in the ‘Join Kind’:

The following diagram helps to visualise what each ‘Join Kind’ means:

In this case, I am going to use the default ‘Left Outer’ join as I want to keep everything in Country, and pull in any matching data from Country-Series.  Power Query has calculated that this will provide a match for all the rows:

I click OK, and a new query is created:

Merge1 contains all the columns from Country and a new column called Country-Series, in which each value is a Table.  If I use the icon next to the column name, I can extract the data from each of the Table values:

I already have the country, so I just need to select Data Sources, and I don’t need a prefix for this as it is a new column name:

I have the new column.  Next time, I’ll look at other ways I can achieve this result.

Come back next time for more ways to use Power Query!