Power Query: Project Population – Part 3
3 May 2023
Welcome to our Power Query blog. This week, I keep on keeping on the transformation of 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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image1-1682010918.png/c8f4d74d3dd91801f4fc065ed038dd13.jpg)
I have downloaded the Excel file, and in Part 1, I extracted the queries I needed:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image2-1682010969.png/47928a4c9b79320d95781b1f9c205fb5.jpg)
I reduced the data by selecting only the columns I wanted to keep:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image3-1682010991.png/ade79e1b5856658e7b1cdfcd417f44d0.jpg)
Last time, I identified and removed any unnecessary rows:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image4-1682011032.png/7b4358c8088524f82761c48f3d4608de.jpg)
I also transformed the System of National Accounts column:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image5-1682011057.png/93070386a844e1b1c9fe61f30e0d59a4.jpg)
I renamed these steps to make it easier to follow the transformations:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image6-1682011076.png/98b3e3d740f89e6a6ae559513e3558c8.jpg)
This time, I am going to look at the column Latest Population Census:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image7-1682011097.png/6e8c6c0679468a640e6553f306d03fed.jpg)
This column currently contains mixed data types, which is why Power Query has given it a type of ‘Any’. I would like to transform this column into two [2] columns, one with the year, and one with any additional notes. On the Transform tab, I choose to split the column ‘By Positions’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image8-1682011119.png/a02d6ca8d8cc3568da09861022abd1ea.jpg)
This prompts me for where to split the column, remembering that Power Query counts from zero [0]:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image9-1682011136.png/d1284e9b1ae5a413cca736139734f3dc.jpg)
Clicking on ‘Advanced options’ also indicates how to enter multiple positions to split the data. For this example, I just need to split at position 4, however, if I only enter 4, then the column after position 4 would be retained. In order to preserve the year and have two [2] columns, I need to enter ‘0,4’ in the Positions box. I take the default to ‘Split into Columns’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image10-1682011159.png/054c554b7fde00315d8d80d8069b3d1d.jpg)
This gives me two [2] new columns.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image11-1682011178.png/981b1745d6bcc521b175a08f34e1f98a.jpg)
Note that I did not have an option to name my split columns. I can add a step to rename them, but instead I will change the M code in the ‘Split Column by Positions’ step:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image12-1682011204.png/8da38bc831267e9567c660572f7d0998.jpg)
I change the code from:
= Table.SplitColumn(Table.TransformColumnTypes(#"Changed SNA
to Number", {{"Latest population census", type text}},
"en-GB"), "Latest population census",
Splitter.SplitTextByPositions({0, 4}), {"Latest population census.1",
"Latest population census.2"})
to:
= Table.SplitColumn(Table.TransformColumnTypes(#"Changed SNA
to Number", {{"Latest population census", type text}},
"en-GB"), "Latest population census",
Splitter.SplitTextByPositions({0, 4}), {"Latest population census",
"Population census notes"})
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image13-1682011274.png/0a9333091401bf8552ac38d70e8076d9.jpg)
I rename the step, and delete ‘Changed Type1’. It no longer uses the correct column names, and it left Latest population census with data type ‘Text’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image14-1682011299.png/7f88c013438de8c68ac05d89aa2b2fd3.jpg)
Next time, I will look at why the column Latest population census was not assigned a numerical data type.
Come back next time for more ways to use Power Query!