cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EllieSim
Helper II
Helper II

Matching and returning a list of all matching words within a number of columns in a new table column

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

Power BI Word Match.PNG

2 ACCEPTED SOLUTIONS
MFelix
Super User III
Super User III

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
Community Support
Community Support

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"

word table.png

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:

table result.png

Here is my sample file that hopes to help you, please try it: PBIX 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

10 REPLIES 10

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors