Power Query: Project Population – Part 5
17 May 2023
Welcome to our Power Query blog. This week, I complete the first query that I downloaded 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-1683902847.png/504dae75a10c7d12f09f763647992b7d.jpg)
I have been transforming the data, and last week, I created and tidied the Latest population census column.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image2-1683902877.png/5e2a275be42b612361b2018e9089da72.jpg)
I also kept the Population census notes, which I need to tidy up. To begin, I move the column after Latest population census. I can do this by selecting the column and dragging it to the end, or by right-clicking and choosing to Move ‘To End’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image3-1683902914.png/0912750269b7615ad6e9093f62173423.jpg)
I examine the contents of the column by looking at the values on the Filter dialog:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image4-1683902940.png/0a109defa864ea9371cdfdc1492e1b41.jpg)
I need to remove the full stops (.) from the beginning of the some of the values. I can do this by using ‘Replace Values’ from the right-click menu:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image5-1683902967.png/5908815f34b583125474c59c82e5cdb7.jpg)
When I click OK, my data looks better, but I need to remove the leading spaces:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image6-1683902990.png/7a890b86d5b2b7b9491fcf16da881be6.jpg)
I can do this by using ‘Trim’ from the Format dropdown on the Transform tab:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image7-1683903011.png/0e66cbda2f38ef5bd8610c0eb6bee8ef.jpg)
My data looks better. However, the recent steps are a little ambiguous:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image8-1683903033.png/f4dafed6f9d445a3766c41da67ecff35.jpg)
Renaming them will make it easier to see what I have done:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image9-1683903053.png/de037fa3fd8758ad1f444848c3033b24.jpg)
Moving on to the National accounts base year column, a quick look at the data might suggest that I use the same approach that I used for Latest population census, where I converted the column to a number to easily identify any values that are not years in order to remove them.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image10-1683903095.png/85266d0ea8ef7d57e909bb0cb356b770.jpg)
However, if I view the values on the Filter dialog, I see that there are combined year values, which cannot be expressed as a number:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image11-1683903121.png/50cfc557f709c6bea45b7cdf175e0cd8.jpg)
In order to preserve this data, I am going to use a different approach. If the first four [4] characters are not numeric, then I want to replace the current value with null. I go to the ‘Add Column’ tab, where I choose to Extract ‘First Characters’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image12-1683903179.png/cc658d8b013a879a664f6a870b0280b7.jpg)
This takes me to a dialog where I can select the first four [4] characters. Note I cannot choose the name of the new column, but as it will be temporary, this doesn’t matter here.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image13-1683903213.png/678202e5528c3389e8ba21d0c61e5629.jpg)
This gives me a new column First Characters containing the first year, which I can change to data type ‘Whole number’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image14-1683903259.png/bbe11a16ad759d6bc105c82c78044084.jpg)
This has identified the values that did not contain a year as errors. I can right-click and choose to ‘Replace Errors’. I shall replace them with null:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image15-1683903297.png/e44649e74eb8af8988bd6fa3911f0156.jpg)
Now I can add a ‘Conditional Column’ from the ‘Add Column’ tab:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image16-1683903324.png/0f3187369b9c9bc90bf0ef974cb07167.jpg)
In the new column name, I have capitalised ‘Accounts’ so that I am not trying to use the same name as the existing column. If the temporary column First Characters is null (meaning that National accounts base year did not have a year in the value), then the new column is set to null, otherwise it will have the same value as National accounts base year.
This gives me a new column, and I can delete National accounts base year and First Characters.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image17-1683903378.png/e9f7c2ffa3402fdf7b1df4e4b4348e73.jpg)
I tidy up by changing the new column to data type Text, and rename the steps.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image18-1683903406.png/5ebe1c2e8dc05961dd490f890b7e4c72.jpg)
Now that all the columns in this query have consistent data, next time, I will look at the data that I can add from the other queries I imported.
Come back next time for more ways to use Power Query!