Power Query: Project Population – Part 11
5 July 2023
Welcome to our Power Query blog. This week, I merge more 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-1688408658.png/aed178f089abce515d8890b88d6a4451.jpg)
I have been transforming the data, and last week, I prepared the Data query.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image2-1688408742.png/e360d2eeddf6236b0bc2de9e7d1c6cd0.jpg)
This time, I will look at some ways to combine my Data and Country queries. However, I need to keep in mind
that the rows in Data do not only correspond to individual countries:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image3-1688408783.png/a1b718952de2d05cc2addbb9404b8b08.jpg)
I have a mixture of data, some for individual countries and some for regions. I need to ensure I don’t ignore the rows in Data that do not match a country on Country.
I will need to consider the rows from Data in two parts: those that have a match on Country, and those that don’t.
I go back to the query I created by merging Country and Country Series. I refined Merge1 in Part 9:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image4-1688408827.png/758ed1dbb1436a44f2c46f1be6cd79aa.jpg)
I am going to add to this query, by merging it with Data. I choose to ‘Merge Queries’ from the ‘Merge Queries’ dropdown on the Home tab:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image5-1688408848.png/379d3690d0355aa9d5773f11bf2f808f.jpg)
I take the default ‘Left Outer’ join as I want to keep everything in Merge 1, and find matching rows in Data. Since Power Query has calculated that all 217 rows on Merge1 have at least one [1] match, this implies that the remaining rows on Data can be extracted to create a table with more information on regions. I will look at this once I have completed the country-level data. I click OK:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image6-1688408880.png/1644a99031e72f1d2ff096d0dcb91a1e.jpg)
I decide not to change this step to use Table.Join(). I have some shared columns, and I don’t wish to add a prefix to all of the column headings to avoid errors caused by duplicate names. Instead, I expand the data by using the icon next to the Data column heading:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image7-1688408900.png/05a150a626e0e8faffd35dbbc7b7f684.jpg)
I choose to expand everything apart from the Country Codeand Country Name, and by expanding this way, instead of using Table.Join(), I can choose not to have a prefix:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image8-1688408920.png/341345adece3fc2fbeaac4719774c53d.jpg)
Notice that each row is now much wider than it was before. Logically, I might assume that this was caused by a row from Data, but this is not the case, as I’ll show next time.
Come back next time for more ways to use Power Query!