Reply
Highlighted
Super Contributor
Posts: 965
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"
JD1 Frequent Visitor
Frequent Visitor
Posts: 2
Registered: ‎02-15-2017

Re: joinAlgorithm and keyEqualityComparers

Thank you!

Super Contributor
Posts: 965
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...