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 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?

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

25 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    It looks like the documentation for keyEqualityComparers is the same as the joinOptions for Table.FuzzyJoin

     

    https://docs.microsoft.com/en-us/powerquery-m/table-fuzzyjoin

     

    I've played with them a little and gotten them to work, i.e.:

     

      #"Merged Queries" = Table.NestedJoin(Source, {"Text1", "Text2"}, #"VCP-Microsoft-Unifications", {"Name", "Text2"}, "VCP Microsoft Unifications", JoinKind.LeftOuter, [IgnoreCase = true, IgnoreSpace = false]),

     

    Hope that helps.

     

    Zig.

     

  • freelensia's avatar
    freelensia
    Icon for Advocate II rankAdvocate II
    6 years ago

    Hi ImkeF 

    I converted your code into the function below.

     

    (LookInTbl as table, KeywordTbl as table, LookInCol as text, KeywordCol as text) =>
// https://community.powerbi.com/t5/Desktop/joinAlgorithm-and-keyEqualityComparers/m-p/182148#M79842

let
    RelativeMerge = Table.AddColumn(LookInTbl, "RelativeJoin", 
        (Earlier) => Table.SelectRows(KeywordTbl, 
                each Text.Contains(Table.Column(Earlier,LookInCol),Table.Column(KeywordTbl,KeywordCol), Comparer.OrdinalIgnoreCase))),
    ExpandRelativeJoin = Table.ExpandTableColumn(RelativeMerge, "RelativeJoin", {KeywordCol}, {KeywordCol})
in
    ExpandRelativeJoin

    Then called it like this:

    RelativeMerge = SearchColForKeywords(GetWordsTbl, KeywordTbl, "English", "Keywords"),

    But it generated this error.

    Expression.Error: We cannot convert a value of type List to type Text.
Details:
    Value=[List]
    Type=[Type]

     Could you check to see if I wrote something incorrectly?
    Thanks!

    • ImkeF's avatar
      ImkeF
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Hi freelensia ,

      a table reference followed by a column name will return a list and not a text (for the first argument of the Text.Contains-function). Hence the error message. To transform my query into a function you have to operate on a record-level like so instead:

       

      let
    LookInTbl = Table.Buffer(#table({"Key1"},{{"Auto"},{"Bus"},{"Autobus"}, {"Car"}})),
    KeywordTbl = Table.Buffer(#table({"Key2"},{{"Auto"},{"Bus"}})),
    LookInCol = "Key1",
    KeywordCol = "Key2",
    fnRelMerge = (LookInTbl as table, KeywordTbl as table, LookInCol as text, KeywordCol as text) =>
    let
            RelativeMerge = Table.AddColumn(LookInTbl, "RelativeJoin", 
            (Earlier) => Table.SelectRows(KeywordTbl, 
                         each Text.Contains(Record.Field(Earlier, LookInCol), Record.Field(_, KeywordCol), Comparer.OrdinalIgnoreCase))),
            #"Expanded RelativeJoin" = Table.ExpandTableColumn(RelativeMerge, "RelativeJoin", {"Key2"}, {"Key2"})
    in
        #"Expanded RelativeJoin",
    CallFunction = fnRelMerge(LookInTbl, KeywordTbl, LookInCol, KeywordCol )
in
    CallFunction

       

      • freelensia's avatar
        freelensia
        Icon for Advocate II rankAdvocate II
        6 years ago

        This works great! I just wanna share my final function. In this function I added 2 steps: filtering out non-matching rows, and removing the additional column. Hope this helps other people.

         

        {SearchColForKeywords}
(LookInTbl as table, KeywordTbl as table, LookInCol as text, KeywordCol as text) =>

let
    RelativeMerge = Table.AddColumn(LookInTbl, "RelativeJoin", 
        (Earlier) => Table.SelectRows(KeywordTbl, 
            each Text.Contains(Record.Field(Earlier, LookInCol), Record.Field(_, KeywordCol), Comparer.OrdinalIgnoreCase))),
    ExpandRelativeJoin = Table.ExpandTableColumn(RelativeMerge, "RelativeJoin", {KeywordCol}, {"Keywords found"}),
    FilterRows = Table.SelectRows(ExpandRelativeJoin, each [Keywords found] <> null and [Keywords found] <> ""),
    RemoveColumn = Table.RemoveColumns(FilterRows,{"Keywords found"})
in
    RemoveColumn