Power Query: Project Population – Part 13

Welcome to our Power Query blog.  This week, I create a query by extracting rows in Data that have no matches in Country.  

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 solved the formatting issue in my merged query:

I also used Table.Buffer() to speed up the refresh of my merged query, which I renamed Population Estimates by Country.

My changes should speed things up for users of this query.  This time, I’ll take a look at the rows in Data that didn’t link in the ‘Merged Queries’ step.  I will start in Data.

I could merge Data with Population Estimates by Country, but this is not the best choice.   Let’s look at why.  I must start by choosing ‘Merge Queries As New’, otherwise I would be changing Data, and that is already being used to create Population Estimates by Country.  I choose to ‘Merge Queries As New’ from the ‘Merge Queries’ dropdown on the Home tab:

The default ‘Join Kind’ is ‘Left Outer’, which finds those rows on Population Estimates by Country that match a Country Code on Data.  However, Population Estimates by Country contains the same set of Country Code values as Country.  Since Population Estimates by Country has already been merged with Data, I am reading columns I don’t need for this process.  It makes more sense to use Country for this merge. 

Note that the number of rows selected is the same.  However, I want those rows on Data that do not have matches on Country, so I am going to use a ‘Left Anti’ join:

Note that this tells me the number of rows excluded, not the number of rows left.  I click OK to see the results:

This gives me the rows from Data which don’t match a Country Code on Country (or Population Estimates by Country).  Next time, I will tidy up this query.

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