cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
freelensia Helper III
Helper III

Re: joinAlgorithm and keyEqualityComparers

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 Helper III
Helper III

Re: joinAlgorithm and keyEqualityComparers

<still writing...will update later>

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 like this:

You will need to update the column names to your real column names

 

otravers Responsive Resident
Responsive Resident

Re: joinAlgorithm and keyEqualityComparers

@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

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors