Forum Discussion
joinAlgorithm 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 :-) )
tringuyenminh92a slight correction - the correct gal :smileyhappy:
oh, After knowing your correction Sean, I really want to show my admirable to her.
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 :-) )
Hi MarcelBeug,
have you come across this already? Any idea how to use these parameters?
Hi ImkeF Now I'm honoured as well. :smileyembarrassed:
As a matter of fact: yes, I came across this parameter as well.
From the function syntax * I learned that it should be supplied as a list, so I just tried with a silly list to see what would happen:
= Table.Join(KlantTabel, "Klantnr", OrderTabel, "Klant", null, null, {Comparer.OrdinalIgnoreCase})As expected, I got an error message:
Expression.Error: Local evaluation of Table.Join or Table.NestedJoin with key equality comparers is not suported.
So my conclusion is that this functionality would only be usable with query folding.
Otherwise I'm not able to verify this, so I can only provide this information and nothing really conclusive.* I created myself an interesting dashboard that generates function information, including syntax. :smileyvery-happy:
Fantastic example! Thank you for providing it. Do you think the relative merge function you provided below can be modified with a Text.Contains function, so you could essentially do a partial text match merge on two tables?
Sure!
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"
