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: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

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
46,237 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,429 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,816 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,304 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,150 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,735 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,736 Views
0

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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

View All