Power Query: Project Population – Part 6

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 query: Country:

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!