Power Query: Project Population – Part 13
19 July 2023
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!