Power Query: A Join I Made Earlier
16 January 2019
Welcome to our Power Query blog. This week, I look at a neat way of linking tables with similar key data.
When I came across this method of linking key data in tables that is ‘almost’ the same, I wanted to share it! I have two tables of tent data:
and
The key data is very similar, but not quite the same. I want to find out which key data in ‘Owned Tent Types’ could be linked to the data in ‘Hired Tent Types’ – so for example, I want to link ‘Large Blue’ owned tents to ‘Large’ hired tents.
I’m impressed by how easily this can be achieved by using keyEqualityComparers. These comparers determine how the keys on each table are compared with each other. I mentioned them previously in Power Query: Join Me at the Table, where the option to use keyEqualityComparers is provided in M function Table.Join().
This time, I will add a column to the table ‘Owned Tents’, which links the two tables. In this case, I want to join my tables if the sequence of letters in the key in the second table appear in the sequence of letters in the key in the first table. I can then link ‘Large Blue’ from the ‘Owned_Tents’ table to ‘Large’ in the ‘Hired_Tents’ table.
The line of M code that does all this heavy lifting is:
RelativeMerge = Table.AddColumn(Owned_Tents, "RelativeJoin",
(Earlier) => Table.SelectRows(Hired_Tents,
each Text.Contains(Earlier[Owned Tent Type],[Hired Tent Type],
Comparer.OrdinalIgnoreCase)))
This code adds a new column, RelativeJoin. The new column contains a join between my tables where Hired Tent Types contain a sequence of letters that match Own Tent Type, ignoring whether they are upper or lower case.
The use of the ‘Earlier’ syntax is complex to explain. The same function in DAX (the Power Pivot language) is described as:
This returns the current value of the specified column in an outer evaluation pass of the mentioned column.
EARLIER is useful for nested calculations where you want to use a certain value as an input and produce calculations based on that input. In Microsoft Excel, you can do such calculations only within the context of the current row; however, in DAX you can store the value of the input and then make calculation using data from the entire table.
EARLIER is mostly used in the context of calculated columns.
I need to use ‘Earlier’ to allow for my nested comparison. This line of M code creates a table in each entry of my new column as the result, which can be expanded.
The expansion is a simple process, as the table only contains the value of Hired Tent Type if there is a match, and null if there isn’t.
Come back next time for more ways to use Power Query!
