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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image1-1689246645.png/c2523bcd2d40657dac5ec213b6539c74.jpg)
I have been transforming the data, and last week, I solved the formatting issue in my merged query:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image2-1689246943.png/e77e2753eec1d73e5af26218cd31b99a.jpg)
I also used Table.Buffer() to speed up the refresh of my merged query, which I renamed Population Estimates by Country.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image3-1689246964.png/f7f7bd4a01ccad24bef3886bb2b4b841.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image4-1689246984.png/cc088efe8deb1edc8f09d5b4fbdf5f35.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image5-1689247005.png/5ae7e6b52d01eabb8e2ffe336e9b36da.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image6-1689247033.png/a131836f4dc1ba3c210829c1110f1825.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image7-1689247056.png/67a13a9842f667ee99b9fa199a6ada20.jpg)
Note that this tells me the number of rows excluded, not the number of rows left. I click OK to see the results:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image8-1689247076.png/e8918489f85790a576f23ae890adddc9.jpg)
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!