Power Query: Joining the dots

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:

I changed this to:

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():

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: 

To use this function:

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!