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 ChampionNow 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 ChampionHi MarcelBeug,
have you come across this already? Any idea how to use these parameters?
- MarcelBeug
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:
- ImkeF
Hi MarcelBeug,
I'm really impressed by the pace of your learning. Meanwhile I'm learning from you :-)
(Yes, I'm also running this kind of formula-overview, but in Excel in order to do full-text-searches when looking for functions that perform specific tasks :-))
- MarcelBeug
In my Excel version (2016 from O365 subscription), the keyEqualityComparers are not included in Table.Join and Table.NestedJoin, so my research for this parameter was in Power BI Desktop.
