Power Query: Project Population – Part 6
31 May 2023
Welcome to our Power Query blog. This week, I prepare my second query from a public data 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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image1-1684757605.png/93d78ab3794c63888ed845f22ec69101.jpg)
I have been transforming the data, and in Part 5, I finished tidying up the main
query: Country:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image2-1684757689.png/a6f1070afcc533d585aa80aa4a68aadd.jpg)
Now that all the columns in this query have consistent data, I will look at the data that I can add from the other queries I imported. Today, I will look at the Country-Series query to see if there is any information that could be useful.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image3-1684757722.png/1531b32ffd8d09c5124f6c66e18ba77b.jpg)
The first thing I notice is that the headers have not been promoted. This is because the algorithms have not detected a difference between the headings and the other data in the column since everything is of data type text. I can fix this by using the ‘Use First Row as Headers’ option on the Home tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image4-1684759033.png/e471df8270b35b20854826d226494ca3.jpg)
However, since this will also generate a ‘Changed Type’ step, I will first delete the current ‘Changed Type’ step:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image5-1684757770.png/340991d69c144cdd68fc196ee3a9bc53.jpg)
This query gives me information about the source of the population data in the Data query. I decide that I do not need the SeriesCode column, so I select the CountryCode and DESCRIPTION, and right-click to ‘Remove Other Columns’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image6-1684757793.png/1dd38aa3cd5e8efe33a9b9d2a6ab3c5b.jpg)
Ideally, I would like to have one description for each country. I start by selecting both columns and accessing the right-click menu, where I can ‘Remove Duplicates’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image7-1684757815.png/a60a05033a7ab08e47a44d1e609c6b89.jpg)
However, there are still multiple descriptions for some countries. I can use a trick to combine the text. I can use ‘Group By’ which is available on the Home tab, and here, on the Transform tab:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image8-1684757839.png/672af2866b4abdbabfe99b57f22ebfe6.jpg)
I want to ‘Group By’ CountryCode, and concatenate the DESCRIPTION rows. However, since I have both columns selected, the dialog is in ‘Advanced’ mode, and prompting me to ‘Group By’ CountryCode and DESCRIPTION. I will sort this out later.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image9-1684757861.png/4aa9823b50b417fd5c87e693d78b2177.jpg)
I want to create a new column which I call Description, where I will sum the DESCRIPTION rows:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image11-1684757912.png/5605c9dbbea8af2118f61c595128532f.jpg)
Unsurprisingly, Power Query is not happy about summing a text value:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image12-1684757938.png/a2e1b7e84f46b756c664333255f7258b.jpg)
Next time, I will convert the M code into something more acceptable…
Come back next time for more ways to use Power Query!