Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

See when key Fabric features will launch and what’s already live, all in one place and always up to date. Explore the new Fabric roadmap

Reply
Greg_Deckler
Super User
Super User

joinAlgorithm and keyEqualityComparers

‎01-06-2017 08:48 AM

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?



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Labels:
Message 1 of 26
45,897 Views
1 ACCEPTED SOLUTION
ImkeF
Community Champion
Community Champion
In response to tringuyenminh92

‎01-06-2017 11:55 AM

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

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Message 5 of 26
48,089 Views
25 REPLIES 25
ImkeF
Community Champion
Community Champion
In response to JD1

‎05-29-2017 08:20 AM

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-powerq...

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Message 13 of 26
24,787 Views
0
ImkeF
Community Champion
Community Champion
In response to ImkeF

‎01-07-2017 01:14 AM

Hi @MarcelBeug,

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

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Message 6 of 26
19,275 Views
0
MarcelBeug
Community Champion
Community Champion
In response to ImkeF

‎01-07-2017 01:44 AM

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)
Message 7 of 26
18,121 Views
0
ImkeF
Community Champion
Community Champion
In response to MarcelBeug

‎01-07-2017 09:23 AM

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

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Message 8 of 26
16,706 Views
0
MarcelBeug
Community Champion
Community Champion
In response to ImkeF

‎01-07-2017 10:12 AM

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)
Message 9 of 26
16,707 Views
0

Helpful resources

Announcements
May PBI 25 Carousel

Power BI Monthly Update - May 2025

Check out the May 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.

View All