Power Query: Project Population – Part 11

Welcome to our Power Query blog.  This week, I merge more data from a public 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 prepared the Data query. 

This time, I will look at some ways to combine my Data and Country queries.  However, I need to keep in mind that the rows in Data do not only correspond to individual countries:

I have a mixture of data, some for individual countries and some for regions.  I need to ensure I don’t ignore the rows in Data that do not match a country on Country.

I will need to consider the rows from Data in two parts: those that have a match on Country, and those that don’t.

I go back to the query I created by merging Country and Country Series.  I refined Merge1 in Part 9:

I am going to add to this query, by merging it with Data.  I choose to ‘Merge Queries’ from the ‘Merge Queries’ dropdown on the Home tab:

I take the default ‘Left Outer’ join as I want to keep everything in Merge 1, and find matching rows in Data.  Since Power Query has calculated that all 217 rows on Merge1 have at least one [1] match, this implies that the remaining rows on Data can be extracted to create a table with more information on regions.  I will look at this once I have completed the country-level data.  I click OK:

I decide not to change this step to use Table.Join().  I have some shared columns, and I don’t wish to add a prefix to all of the column headings to avoid errors caused by duplicate names.  Instead, I expand the data by using the icon next to the Data column heading:

I choose to expand everything apart from the Country Codeand Country Name, and by expanding this way, instead of using Table.Join(),  I can choose not to have a prefix:

Notice that each row is now much wider than it was before.  Logically,  I might assume that this was caused by a row from Data, but this is not the case, as I’ll show next time.

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