Forum Discussion
Community ChampionjoinAlgorithm and keyEqualityComparers
Now I feel really honoured - thank you guys :-)
Honestly: I haven't recognized these 2 parameters before. And while the MS-documentation holds some things about the joinAlgorithms in here: https://msdn.microsoft.com/en-us/library/mt296614.aspx, there's zero about keyEqualityComparers.
But with regards to the desired relative comparison here (< or >), I dare to say that they probably wouldn't help, as M distinguishes between equality and relational comparisons.
So in order to not let you down here, please have a look at the following query, which performs a relative lookup (and will hopefully appeal to you DAX-masters :-) ):
let Table1 = #table({"Key1"},{{10},{20},{30},{40}}), Table2 = #table({"Key2"},{{9},{19},{29},{39}}), RelativeMerge = Table.AddColumn(Table1, "RelativeJoin", (Earlier) => Table.SelectRows(Table2, each [Key2]<Earlier[Key1])), #"Expanded RelativeJoin" = Table.ExpandTableColumn(RelativeMerge, "RelativeJoin", {"Key2"}, {"Key2"}) in #"Expanded RelativeJoin"(This is nested row-context: Yes, we have evaluation context in M as well & fortunately it doesn't behave like a moving target :-) )
Community ChampionSure!
This query will match all words from Table2 who are somehow included in the strings of Table1 in a case insensitive mode:
let
Table1 = Table.Buffer(#table({"Key1"},{{"Auto"},{"Bus"},{"Autobus"}, {"Car"}})),
Table2 = Table.Buffer(#table({"Key2"},{{"Auto"},{"Bus"}})),
RelativeMerge = Table.AddColumn(Table1, "RelativeJoin",
(Earlier) => Table.SelectRows(Table2,
each Text.Contains(Earlier[Key1],[Key2], Comparer.OrdinalIgnoreCase))),
#"Expanded RelativeJoin" = Table.ExpandTableColumn(RelativeMerge, "RelativeJoin", {"Key2"}, {"Key2"})
in
#"Expanded RelativeJoin"
Thank you!
- ImkeF
If you run into performance issues with these techniques, it's worth checking out if you can partition your tables: http://www.thebiccountant.com/2017/05/29/performance-tip-partition-tables-crossjoins-possible-powerquery-powerbi/