Reply
Highlighted
Super User
Posts: 1,502
Registered: ‎09-06-2015

Re: joinAlgorithm and keyEqualityComparers

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"

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




JD1 Frequent Visitor
Frequent Visitor
Posts: 2
Registered: ‎02-15-2017

Re: joinAlgorithm and keyEqualityComparers

Thank you!

Super User
Posts: 1,502
Registered: ‎09-06-2015

Re: joinAlgorithm and keyEqualityComparers

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries