Power Query: Project Population – Part 2
26 April 2023
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 ) and extract four  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  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!