Power Query: Project Population – Part 10
28 June 2023
Welcome to our Power Query blog. This week, I transform a new 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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image1-1687863407.png/fbf525d00d38e06626311bf4f457bbc2.jpg)
I have been transforming the data and last week, I improved the steps created automatically by Power Query when I used ‘Merge Queries’ to combine the data from Country and Country-Series.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image2-1687863477.png/6e285be36c3146b906b2a984dedfca73.jpg)
This time, I will move on to the Data query and prepare this data.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image3-1687863508.png/4d08218c60ebbb642b225b4375ef6ca7.jpg)
This query appears to contain information about the population of each country. I note that although I have a column with the heading ‘Country Code’ in the first row, when I look at the values underneath ‘Country Name’, I see that the first rows are for a region, not a single country.
I can look at the filter on Column1 to see what values are in this column:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image4-1687863536.png/8097cac84362ad55b2742824e0e05041.jpg)
I have a mixture of data, some for individual countries and some for regions. When I come to merge this data, I will need to ensure I don’t ignore the rows in Data that do not match a country on Country.
I start by cleaning up the Data query. I need to promote the first row to the headings, which I can do from the ‘Home’ tab:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image5-1687863559.png/b62534a1e4388bfdef42cd545665fe95.jpg)
This also generates a ‘Changed Type1’ step for me:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image6-1687863595.png/e1c464944233bdeaca20c0f89a0d0bb5.jpg)
I don’t need the earlier ‘Changed Type’ step, so I can delete it using the cross icon next to it:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image7-1687865054.png/c57edec50f628ecc3df3dc17892dfa52.jpg)
I am prompted to confirm this choice:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image8-1687863633.png/637fc04a63c3b279b2ddf30e0bfd4f57.jpg)
If this were a step that affected the name of a column, or if the subsequent steps relied on the data type being changed, then this could give me problems, but since I know that it will not affect the later steps I can go ahead. Power Query does not have an ‘undo’ option. Therefore, if I were uncertain about whether deleting a step would cause problems, I could make a copy of the query by right-clicking in the Queries pane and choosing to duplicate the query before proceeding.
Having deleted the step, a quick way to see all the columns without scrolling is to use the ‘Choose Columns’ feature on the Home tab:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image9-1687863667.png/8150e412df283caf5445917a6b30ccd3.jpg)
This allows me to see the complete list:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image10-1687863690.png/f3c12ee7aab397de6af8076a3fdbea42.jpg)
I have the Country Name, Country Code, IndicatorName, Indicator Code and then the years from 1960 projected to 2050. I will only uncheck the Indicator Code column, as it’s not giving me any useful information.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image11-1687863710.png/b8bc047f120f50b62f857d7c0943776c.jpg)
Note that the step created is ‘Removed Other Columns’ as it is safer to keep the columns I need, since if any columns are added to Data, my query will be unaffected.
Looking at the data, I can see that there are some categories where all the year columns have null amounts:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image12-1687863731.png/166527bca192f53f1d6f6d00d40edfd2.jpg)
I can remove these rows. I start by selecting all the year columns. The easiest way to select all the years is to select 1960, scroll across to 2050, and then press SHIFT whilst I select column 2050, to select the complete range of years.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image13-1687863871.png/ff94b00fc3d5f62b4537e86ea0d16f5f.jpg)
Next, I merge the columns from the ‘Add Column’ tab:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image14-1687863903.png/7218213ed8b9cf301d9e97e2ccc78273.jpg)
I can take the default settings in the dialog box, as I will be deleting this column soon.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image15-1687863926.png/285fc12dd4fee5e8c42b0ffb464b6a41.jpg)
This gives me one column, which I can filter:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image16-1687863942.png/c41d122e4da0e786abab5fb677169685.jpg)
I choose to ‘Remove Empty’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image17-1687863963.png/d3b55fd9c5ed01f6dec6aa39480d7834.jpg)
I can now delete the Merged column.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image18-1687863991.png/3e60f3daca63a1ddb3c75d6ff425bfa9.jpg)
Next time, I will look at some ways to combine my Data and Country queries.
Come back next time for more ways to use Power Query!