Power Query: Project Population – Part 14
26 July 2023
Welcome to our Power Query blog. This week, I complete the Project Population series.
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-1689248772.png/7dfeb19338a18c8f60be49d42231d590.jpg)
I have been transforming the data, and last week, I created a query by merging Country and Data, using Left Anti join, to get the rows from Data which don’t match a Country Code on Country(or, by extension, Population Estimates by Country).
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image2-1689248799.png/da209add7876d2ae5717ad2736bb0e3d.jpg)
This time, I will tidy up this query. The first thing I shall do is to make the existing Source step more efficient. The M code is currently:
= Table.NestedJoin(Data, {"Country Code"}, Country,
{"Country Code"}, "Country", JoinKind.LeftAnti)
In this case, I definitely do not want to change this to use Table.Join(),since I don’t need any columns from Country. I do however want to use Table.Buffer() to speed up the merge:
= Table.NestedJoin(Table.Buffer(Data), {"Country Code"},
Table.Buffer(Country), {"Country Code"}, "Country",
JoinKind.LeftAnti)
As usual, this has no impact on the appearance of my query:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image3-1689248885.png/59a8d843cbccbb73499a4919eec717cb.jpg)
Since merging always follows the same process, even though I have used a ‘Left Anti’ join to find those Country Code values on Data that did not have a match in Country, I still have a column where I could extract the Country columns:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image4-1689248904.png/02d9e14cd678e8b1169adfd666af2841.jpg)
Unsurprisingly, if I click on the space next to one of the ‘Table’ values, there is not much to see:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image5-1689248926.png/06fb730ddeee658f098948d5a9cb5710.jpg)
Therefore, I delete the Country column by selecting it and pressing the Delete key.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image6-1689248950.png/bc17f6754478e14cdc9c0becb9936ca7.jpg)
Since I know that the Country Code column in this query is representing more than just one country, I change the column name to Region Code. Similarly, I rename Country Name to Region Name. I also rename the query Region Data:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image7-1689248980.png/7adf3f732505eb19f330295698c51025.jpg)
I could wait until I upload the query to see how many rows are uploaded, but there is another way. I select the Region Code column, and on the Transform tab, I choose ‘Count Values’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image8-1689248999.png/f94fea988066354ab0699f30b64901db.jpg)
This will tell me how many rows in this column have values other than blank or null.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image9-1689249031.png/5f1ad31cb48502c6ba7ab9dc1e7c5468.jpg)
My answer is 5,940 rows. Note that this uses List.NonNullCount(), so if I wanted to include this data in my query, I could put this M code into a ‘Custom Column’ using the option on the ‘Add Column’ tab:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image10-1689249051.png/98ae5eccf0aaf24d60293bc4d3a7529b.jpg)
The number of rows is now shown with my data and I move it to the beginning of the query by right-clicking on the Count column and choosing ‘Move to Beginning’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image11-1689249071.png/378ac6d51182cc28f3b0efc23c1c23d0.jpg)
Finally, I will load the queries I have created into Excel (having changed the data type of Count to a Whole Number). I choose ‘Close & Load’, and right-click on the query I wish to load to the current sheet. This presents me with the ‘Import Data’ dialog, where I choose to load to Table, and select a cell on the existing worksheet:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image12-1689249117.png/6c0c6db1d605d0ee8240ade19089b367.jpg)
I can repeat this process for the Region Data query:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image13-1689249136.png/aa863ab7f6bb74ee85a6343d947e85f4.jpg)
I’ve proved that the count is correct!
Come back next time for more ways to use Power Query!