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.
I have been transforming the data, and in Part 5, I finished tidying up the main
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.
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.
However, since this will also generate a ‘Changed Type’ step, I will first delete the current ‘Changed Type’ step:
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’:
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’:
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:
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.
I want to create a new column which I call Description, where I will sum the DESCRIPTION rows:
Unsurprisingly, Power Query is not happy about summing a text value:
Next time, I will convert the M code into something more acceptable…
Come back next time for more ways to use Power Query!