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.
I have been transforming the data, and last week, I prepared the Data query.
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:
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:
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:
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  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:
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:
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:
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!