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

JD1's avatar
JD1
New Member
9 years ago

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?

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

Sure!

This query will match all words from Table2 who are somehow included in the strings of Table1 in a case insensitive mode:

 

let
    Table1 = Table.Buffer(#table({"Key1"},{{"Auto"},{"Bus"},{"Autobus"}, {"Car"}})),
    Table2 = Table.Buffer(#table({"Key2"},{{"Auto"},{"Bus"}})),
    RelativeMerge = Table.AddColumn(Table1, "RelativeJoin", 
            (Earlier) => Table.SelectRows(Table2, 
                         each Text.Contains(Earlier[Key1],[Key2], Comparer.OrdinalIgnoreCase))),
    #"Expanded RelativeJoin" = Table.ExpandTableColumn(RelativeMerge, "RelativeJoin", {"Key2"}, {"Key2"})
in
    #"Expanded RelativeJoin"
  • JD1's avatar
    JD1
    New Member
    9 years ago

    Thank you!

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

  • edwash91_go's avatar
    edwash91_go
    Regular Visitor
    5 years ago

    This SAVED ME Major!

     

    It was small, but gave me much to read and apply!

     

    I even added in the Table.FirstN to limit my matches to 1. 

     

    let Table1 = Table.Buffer(TransactionsOnly), /* Bank Transactions already cleaed in another query*/ Table2 = Table.Buffer(MatchMatrix), /*Excel File With FROM and TO Columns to be used in my compare, from another query*/ FakeMerge = Table.AddColumn( Table1,"RelMerge", (OrigTable) => Table.FirstN( Table.SelectRows( Table2, each Text.Contains(OrigTable[TheDESCR],[From] ,Comparer.OrdinalIgnoreCase) ),1) /*Limits to first row match*/ ), #"Expanded RelMerge" = Table.ExpandTableColumn(FakeMerge, "RelMerge", {"To"}, {"To"}), #"Replaced Value" = Table.ReplaceValue(#"Expanded RelMerge",null,"Other",Replacer.ReplaceValue,{"To"}), #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Amount", type number}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Source] = " Capital 1 - 1324")) in #"Filtered Rows"

     

    ignore the stuff below I am still troubleshooting somethings, but wanted to share this! very powerful stuff! 

     

    With my approach as new transactions appear, I can easily update the excel file and it will now have a new value to test against and convert 

  • JuanBolas's avatar
    JuanBolas
    Icon for Helper II rankHelper II
    4 years ago

    Hello ImkeF

     

    I'm a newbie and trying to get my head around this which I am unable to do so completely.

     

    How and where do I enter the function?

     

    If I have two tables and the desired output is Category in Table 1:

     

    Table 1                                                                        Table 2

    Merchandise                    Category                            Sub-category           Category

    ============================              =========================

    Cornmeal                                                                    Corn                          Corn

    Bryers Cornstarch                                                       Wheat                        Wheat

    Wenn cornstarch                                                        Car                           Automobile &  Spares

    Wheat BrandA                                                            Ford                         Automobile &  Spares

    Wheat Brand B                                                           Spares                       Automobile &  Spares

    Wheat Germ                                                              Paper                       Paper & Carboard

    Car                                                                             Cardboard               Paper & Carboard

    Ford                                                                           

    Paper Brand 1

    Paper Brand 2                                                             

    Cardboard box                                                           

    Automobile spares 

    Spares                                                                        

     

    I need to identify if the 'Category' column's string in Table 1 contains any of the values in 'Sub-Category' in Table 2 and put the 'Category' from Table 2 in 'Category' in table 1.  The match should be case insensitive.

     

    Thanks in advance for your help!