Solved: Matching and returning a list of all matching word... - Page 2

EllieSim · ‎04-29-2020

Hello,

I need help to match and return the list of matching values ( in a new column, separated by a comma ideally) that are located within several columns in my table for each row to the values within another column.

I need to look for values in the columns Category, Family and Bham_Name and return a list for any words within these columns that match a word within the Bham_NRM_Level4/5_Code corresponding row. If a match does not exist, to return a string that says Warning.

What is the best way to do this and also is there a way to account for the difference in language where words are referred to differently, such as Pipe, Pipes, Pipeline, Pipework etc? Perhaps base the match on 4 minimum number of chatterers instead of the whole word?

Many thanks for your help. 🙂

MFelix · ‎04-29-2020

Hi @EllieSim ,

Believe you have an issue with defining the word you need but try to add the following calculated column:

find =
VAR find_text =
    LEFT (
        RIGHT (
            'Table'[Level 4/5];
            LEN ( 'Table'[Level 4/5] ) - SEARCH ( " "; 'Table'[Level 4/5]; 1 )
        );
        4
    )
RETURN
    IF (
        SEARCH ( find_text; 'Table'[Category]; 1; BLANK () )
            + SEARCH ( find_text; 'Table'[Bham_Name]; 1; BLANK () ) > 0;
        BLANK ();
        "Warning"
    )

Columns may need adjustment.

This is only returning the Warning part. Please tell me if this work as you need.

On the other part returning the list of words taking into account that this can be any given word what do you need for example:

Drainage Pipe you need Drainage Pipe or just Pipe?

Regards

Miguel Félix

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

Proud to be a Super User!

Check out my blog: Power BI em Português

View solution in original post

v-yingjl · ‎04-30-2020

Hi @EllieSim ,

I thought there is a subset relationship between 'Level 4/5 code' and other three columns so I created a Words table in advanced editor first:

let
    OriginTable = Table,
    TableA = Table.RenameColumns(Table.ExpandListColumn(Table.TransformColumns(Table.SelectColumns(OriginTable,{"Category"}), {{"Category", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Category"),{"Category","Word"}),
    TableB = Table.RenameColumns(Table.ExpandListColumn(Table.TransformColumns(Table.SelectColumns(OriginTable,{"Family"}), {{"Family", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Family"),{"Family","Word"}),
    TableC = Table.RenameColumns(Table.ExpandListColumn(Table.TransformColumns(Table.SelectColumns(OriginTable,{"Bham_Name"}), {{"Bham_Name", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Bham_Name"),{"Bham_Name","Word"}),
    FinalTable = Table.Combine({TableA,TableB,TableC}),
    #"Removed Duplicates" = Table.Distinct(FinalTable)
in
    #"Removed Duplicates"

This Word tabls is spilts these three columns based on ' ' identifier and combine them with different strings.

Then I created a calculated column like this:

Column = 
COALESCE (
    CALCULATE (
        MAX ( 'Words'[Word] ),
        FILTER (
            'Words',
            CONTAINSSTRING ( 'Table'[Category], 'Words'[Word] )
                && CONTAINSSTRING ( 'Table'[Family], 'Words'[Word] )
                && CONTAINSSTRING ( 'Table'[Bham_Name], 'Words'[Word] )
                && CONTAINSSTRING ( 'Table'[Bhan_Code], 'Words'[Word] )
        )
    ),
    "Warning"
)

You will get the following result which is your possibly expected: