Power Query: Project Population – Part 2

Welcome to our Power Query blog.  This week, I continue to transform selected data from a public 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 downloaded the Excel file, and last week I extracted the queries I needed to Power Query:

I reduced the data by selecting only the columns I wanted to keep:

This time, I will identify and remove any unnecessary rows and continue to transform the data.  Looking at the country names, I can see that some are regions and not countries:

For example, row 2 is ‘African Eastern and Southern’ and row 8 is ‘Arab World’.  Since I only want the country data, I can remove these by filtering on Currency Unit, and choosing ‘Remove Empty’:

This leaves me with a smaller dataset of 13 columns and 217 rows.

Now I can turn my attention to transforming the remaining data.   The System of National Accounts column has excess words:

I could split the column into numerical and text data, using the ‘Split column’ feature on the Transform tab, but I only need the year, so the remaining column would just need to be deleted. 

Instead, I can extract the year as a range, since it always appears in the same position:

I want to start at position 17 (remembering the first position is zero [0]) and extract four [4] characters:

This gives me the years where the data is available, and I can change the data type to ‘Whole number’:

This is a good point to look at the names of the steps that have been generated:

The first six [6] steps are fine, as I am reducing the data.  However, ‘Extracted Text Range’ and ‘Changed Type 1’ could be improved:

Changing the names has been achieved simply by right-clicking on the step and clicking ‘Rename’.  Note that I could have chosen to remove spaces from the step names, which would remove the leading hash (#) from the step names in the Advanced Editor:

This depends how familiar you (and anyone else that might need to understand the query) are with M code.

That’s it for this week, next time I’ll continue transforming the queries.

Come back next time for more ways to use Power Query!