cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User IV
Super User IV

joinAlgorithm and keyEqualityComparers

In looking at the documentation for Table.Join and Table.NestedJoin, there is a brief mention of optional parameters of joinAlgorithm and keyEqualityComparers. However, I cannot find any information on how to use these. Does anyone have any examples of these? 

 

I particularly want to know if these can be used to support unequal matching conditions, for example, only join if > or < versus =.

 

@ImkeF?


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: 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 🙂 )

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

17 REPLIES 17
tringuyenminh92 New Contributor
New Contributor

Re: joinAlgorithm and keyEqualityComparers

haha, i think you are asking correct guy @Greg_Deckler, @ImkeF will like this Heart

Sean Super Contributor
Super Contributor

Re: joinAlgorithm and keyEqualityComparers

@tringuyenminh92a slight correction - the correct gal Smiley Happy

 

tringuyenminh92 New Contributor
New Contributor

Re: joinAlgorithm and keyEqualityComparers

oh, After knowing your correction @Sean, I really want to show my admirable to her.

Super User III
Super User III

Re: 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 🙂 )

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

Super User III
Super User III

Re: joinAlgorithm and keyEqualityComparers

Hi @MarcelBeug,

have you come across this already? Any idea how to use these parameters?

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




MarcelBeug Super Contributor
Super Contributor

Re: joinAlgorithm and keyEqualityComparers

Hi @ImkeF Now I'm honoured as well. Smiley Embarassed

 

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. Smiley Very Happy

Specializing in Power Query Formula Language (M)
Super User III
Super User III

Re: joinAlgorithm and keyEqualityComparers

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

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




MarcelBeug Super Contributor
Super Contributor

Re: joinAlgorithm and keyEqualityComparers

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.

Specializing in Power Query Formula Language (M)
JD1 Frequent Visitor
Frequent Visitor

Re: joinAlgorithm and keyEqualityComparers

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?

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors