Forum Discussion

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
9 years ago
Solved

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 ...
Need Help
  • ImkeF's avatar
    ImkeF
    9 years ago

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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi ImkeF ,

 

Sorry for replying to an old post, but needed your valuable help to my query as this problem looked similar to mine:

 

I have a single column table named WordList containing a unique list of values, whose each word value i want to do a partial match with phrases in multiple Attribute columns in another table named Data Extract and get a count of the item codes that show these word values. I want to count only the First occurence of a word value for each item

 

e.g. A word value may occur in a single phrase multiple times OR across multiple Attributes for that Item, but it should be counted as 1.

 

WordList Table:

 

Words

auto

bus

ingenius

natural

color

 

 

DataExtract Table:

 

ItemID    Attr1                       Attr2                        Attr3                      Attr4

10001      natural and soft     put in auto mode      so natural!               get auto; get auto mode

10002      bus at stop             natural icecream       boy is ingenius        more power

10003      let me out!             promote goodwill     simple simon           auto model

10004      matching color      natural color              natural essence       figure it!

10005      god will                  meet at bus stop       similar cases            rosetta stone

10006      mud ringworm       helper columns         rainbow color          geneva convention

10007      masked man          tenor voice                silica gel                   natural smell

10008      microcosm             guns of navarone      bloated cell              enigmatic man

10009      cellular sky             i am natural               color coated            ingenius of him!

10010      ingenius body        maker chambers       old story                  minor changes

 

 

Output Table:

Words         ItemCount          %ItemCount

auto                   2                              =2/10

bus                     2                              =2/10

ingenius             3                              =3/10

natural               5                              =5/10

color                  3                              =3/10

 


How do i find the Item Counts?

 

ImkeF's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
7 years ago

not sure what you mean with "partial match":

1) Would "Autobus" return a match (or even 2)?

 

and if so: only for "bus" or also for "auto" - leading to the second question:

 

2) Shall this be case sensitive or not?

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi ImkeF ,

     

    Thanks for your reply. :)

     

    Sorry, i added a new post later with the complete issue on the Desktop board. Got a solution using DAX in PowerBI, but needed a Power Query solution in Excel as i am on Excel 2016 and as the data is huge.

     

    To answer your question - sorry again for the wrong word used ie. partial. What i simply meant is:

     

    Only search for each word given in the Wordlist Table in the phrases found in the Attribute columns in DataExtract table. and get a count of the items that have atleast one occurence of each word. Also, it will be case-insensitive.

     

    You may reply to that new post. Again, apologies for the replying to an old post, instead of starting a new post.