Forum Discussion

Greg_Deckler's avatar
Greg_Deckler
Community 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 :-) )

freelensia's avatar
freelensia
Advocate 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
Community 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
    Advocate 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
    • freelensia's avatar
      freelensia
      Advocate II
      6 years ago

      Hi guys, just sharing my improved function one last time.

       

      There was a small problem in my previous solution. If multiple keywords matched, the function will duplicate the row. For example if we have keywords: "cats", "dogs", "birds" and the cell has this text "The dogs chased the cats while the birds watched", you will have 3 identical rows each with one keyword that matched.

      So we needed to group and concatenate matched keywords.

       

      //Keep rows of table LookInTbl if its value in LookInCol contains any keyword in KeywordCol of KeywordTbl
//Used like this: 
//    NextStep = KeepRowsIfFindAnyKW(LastStep, "Word ID", KeywordLang, KeywordsTbl, "Keywords"),

(LookInTbl as table, IDCol as text, LookInCol as text, KeywordTbl as table, KeywordCol as text) =>

let
    //Search a column for any text from another table https://community.powerbi.com/t5/Desktop/joinAlgorithm-and-keyEqualityComparers/m-p/1013429#M479534
    RelativeMerge = Table.AddColumn(LookInTbl, "RelativeJoin", 
        (Earlier) => Table.SelectRows(KeywordTbl, 
            each Text.Contains(Record.Field(Earlier, LookInCol), Record.Field(_, KeywordCol), Comparer.OrdinalIgnoreCase))), //case insensitive
    ExpandRelativeJoin = Table.ExpandTableColumn(RelativeMerge, "RelativeJoin", {KeywordCol}, {"Keywords Found"}),
    FilterRows = Table.SelectRows(ExpandRelativeJoin, each [Keywords Found] <> null and [Keywords Found] <> ""),

    // Group by first keyword and keep all columns https://community.powerbi.com/t5/Power-Query/Group-by-date-and-keep-all-columns/td-p/794601
    GroupRows = Table.Group(FilterRows, {IDCol}, {{"First Keyword Found", each List.Max([Keywords Found]), type text}, {"All Data", each _, type table}}),
    ExpandAll = Table.ExpandTableColumn(GroupRows, "All Data", List.RemoveItems(Table.ColumnNames(GroupRows[All Data]{0}), {IDCol})), //dynamically expanding all columns except Word ID and Keywords found https://eriksvensen.wordpress.com/2019/10/05/powerquery-control-the-expand-columns-so-it-includes-new-columns/
    SelectRows = Table.SelectRows(ExpandAll,each [First Keyword Found]=[Keywords Found]),
    RemoveCols = Table.RemoveColumns(SelectRows,{"First Keyword Found", "Keywords Found"})
in
    RemoveCols

       

    • otravers's avatar
      otravers
      Community Champion
      6 years ago

      freelensia can you please share a sample file with your function invoked to add a custom column to a table? It looks exactly like what I need but I'm struggling to pick the right table and column references (I'm easily confused!).

       

      Edit: for reference, I ended up doing a partial match by using a cartesian product as described in this post:

      https://www.myspreadsheetlab.com/power-query-solution-partial-match-count-with-a-condition/

       

      Note that this will create a bunch of duplicate rows, so make sure to have an index column for your initial rows (before the cartesian product), then sort (decreasing) by the match column (the one with Text.PositionOf) then use Table.Buffer before you can finally remove duplicates:

      https://www.youtube.com/watch?v=rqDdnNxSgHQ

      • freelensia's avatar
        freelensia
        Advocate II
        5 years ago

        otraverspl see my improved code below.