Please note javascript is required for full website functionality.


Power Query: Joining the dots

15 February 2023


Welcome to our Power Query blog.  This week, I return to the topic of Table.Join().


Regular readers of this blog may recall Power Query: More Merging Matters from a couple of years ago.  I am going to revisit this, as I now have an improvement.  Allow me to refresh your memory.  I needed to combine data from two tables:

I extracted both tables into Power Query and called the queries Charges and Descriptions.  I started with Charges and opted to ‘Merge Queries as New’ from the Home tab.

I chose to use the default ‘Left Outer’ join and viewed the resulting M code.

The M code generated was:

Table.NestedJoin(Charges, {"Table_Key"}, Descriptions, {"Table_Key"}, "Descriptions", JoinKind.LeftOuter)

I changed this to:

= Table.Join(Charges, {"Table_Key"}, Descriptions, {"Table_Key"}, "Descriptions", JoinKind.LeftOuter, JoinAlgorithm.SortMerge)

The use of JoinAlgorithm.SortMerge is explained in Power Query: More Merging Matters.

When I applied my code, I had a problem:

Since some of the column names in Charges and Descriptions were the same, applying Table.Join() meant that I encountered an error.  Table.Join() extracts the selected columns from the joined table and adds them to the current table.  To solve this at the time, I manually renamed the columns in Descriptions, and amended the join information to get my result:

However, there is another way I could have achieved this.  There is a function called Table.PrefixColumns():

Table.PrefixColumns(table as table, prefix as text) as table    

This returns a table where all the column names from the table provided are prefixed with the given text, prefix, plus a period / full stop (.) in the form ‘prefix.ColumnName'.

If I change the following M code: 

Table.NestedJoin(Charges, {"Table_Key"}, Descriptions, {"Table_Key"}, "Descriptions", JoinKind.LeftOuter)

To use this function:

= Table.Join(Charges, {"Table_Key"}, Table.PrefixColumns(Descriptions,"Descriptions"), {"Descriptions.Table_Key"}, "Descriptions", JoinKind.LeftOuter, JoinAlgorithm.SortMerge)

This then avoids the need to manually rename columns and the columns in the query Descriptions remain unaffected (which would also avoid impacting any other queries that use Descriptions).  I do need to change the join to use the amended column name Descriptions.Table_Key :

This makes the use of Table.Join() even more efficient.


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