Please note javascript is required for full website functionality.


Power Query: Project Population – Part 9

21 June 2023

Welcome to our Power Query blog.  This week, I continue combining my queries 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. 

I have been transforming the data, and last week, I used ‘Merge Queries’ to combine the data from Country and Country-Series

I’d like to take a closer look at the Source step for this query (the first item in the ‘APPLIED STEPS’ section).

The M code is:

= Table.NestedJoin(Country, {"Country Code"}, #"Country-Series", {"CountryCode"}, "Country-Series", JoinKind.LeftOuter)

The M function used here is Table.NestedJoin().  This is the default join method, but I can use a more efficient one.  I can use Table.Join() instead:

= Table.Join(Country, {"Country Code"}, #"Country-Series", {"CountryCode"}, JoinKind.LeftOuter)

At first glance, this appears to have had the same effect.

However, notice that the code no longer specifies the name of the output column.  If I look at the column created,

I already have the Data Sources column; this means that the ‘Expanded Country-Series’ step is now redundant:

I should delete this step since I have merged the data from Country and Country-Series with one [1] step. 

I would use the Table.Join() method over the Table.NestedJoin() method when I want to merge most of the columns from both tables, otherwise I would simply be adding columns to delete them again.

Note that in order to use Table.Join(), there should be no common column names, otherwise an error would be encountered.  This doesn’t mean that I can’t use Table.Join() if there are shared column names.  I looked at a way of dealing with this in Joining the dots.

That’s it for this time; next time, I will move on to the Data query…

Come back next time for more ways to use Power Query!