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

[EllieSim]

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 · Accepted solution]

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?

[v-yingjl · Accepted solution]

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:

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.

[EllieSim]

Hi Yingjie Li,

Thank you so much for the effort you have put in this solution. I am not familiar with the M language at this stage I'm afraid. The solution seems really rebust however, I have not been able to replicate it at my end so far.

The name of the table which contains the Family, Category, Bham_Name and Bham_NRM_Level4/5_Code is 'Bham NRM Exists As Imported'. There are other tables which contain columns with the same names within my report, so the column names are not exclussive. Could you please let me know where do I need to reflect the name of this table which the new Words text table is looking for values?

Do I then go to create a blank query in the query editor?

Many thanks for your help,

Ellie

[v-yingjl]

Hi @EllieSim ,

As I previous posted, the word table is a copy of the data table orignally. Then it spilt the three columns based on " " identifier into three tables like table A, B, C. After this it combines threee columns and delete repeated fields and produce a final word table.

You can create a blank query and click Advanced Editor to try to use these M codes. 

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.

[EllieSim]

Hi @v-yingjl 

Thank you very much for the clarification. I managed to replicate it successfully. It doesn't exclude words like conjunctions (in, at etc) but it does match the words and returns a warning as described.

Many thanks,

Ellie

[MFelix]

Hi Ellie,

As we discussed in private message using you example file I have setup a new column that return the values based on the text of the Level4/5 in this measure I have also included the exceptions if you want to add them on the filtering that is made.

Output to a Table = 
VAR Sentence = 'Bham NRM Exists'[Bham_NRM_Level_4/5_Code]
VAR SentenceCleaned = " " & Sentence & " "
VAR LengthOfSentence = LEN(SentenceCleaned)
VAR PivotedSentence  = 
    ADDCOLUMNS(
        GENERATESERIES(1;LengthOfSentence) ;
        "Letter" ; 
        MID(SentenceCleaned;[Value];1)
    )  
var Boundaries = 
  ADDCOLUMNS(
    PivotedSentence ;
    "PrevSpace"; MAXX(FILTER(PivotedSentence ;''[Value] < EARLIER([Value]) && [Letter] = " ");[Value]) + 1;
    "NextSpace"; MINX(FILTER(PivotedSentence ;''[Value] > EARLIER([Value]) && [Letter] = " ");[Value]) - 1 
       )
VAR TableOfWords =        
    SELECTCOLUMNS(
        SUMMARIZE(
             FILTER(Boundaries;NOT [Letter] IN {" "}) ;
            [PrevSpace]
            );"Word Position";[PrevSpace]
            ) 
    
VAR TableOfWords2 = 
    ADDCOLUMNS(
        TableOfWords;
        "Word";
        CONCATENATEX(
            FILTER(
                Boundaries;[PrevSpace]=[Word Position]);
                [Letter];
                ;
                [Value]
                )
        )
VAR RankedWords = 
    ADDCOLUMNS(
        FILTER(TableOfWords2;NOT [Word]  In {"Word1"; "word2"; "word 3"} ); //Added code
        "Word Number" ; 
        RANKX(TableOfWords2;[Word Position];;ASC)
        )
            
RETURN 
IF(
      SUMX(RankedWords;
           SEARCH(
                LEFT([Word];4);
                'Bham NRM Exists'[Bham_Name]
                ;;0
               )
          ) 
+
      SUMX(RankedWords;
           SEARCH(
                LEFT([Word];4);
                'Bham NRM Exists'[Category]
                ;;0
               )
          )
> 0;
      "";
      "Warning"
     )

I have added this code to the measure NOT [Word] In {"Word1"; "word2"; "word 3"} this is were you need to add all the Exception be aware that you may need on some of them to have and additional space in the end of the word so would be "word1 " but try matching it to your text.

This solution is based on this two post below:

https://radacad.com/quick-dax-word-count

https://powerpivotpro.com/2014/01/containsx-finding-if-a-value-in-table-1-has-a-matching-value-in-ta...

If you need any more assistance please tell me.

[v-yingjl]

Hi @EllieSim ,

I have attached the sample .pbix file in the end of my first post, please check and try it.

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.

[adrisarmientop]

Good afternoon

I am in a working group which is being presented with the following inconvenience and would like to know if they could help me solve it.

I have a dashboard that is recalculating the mix effect, price and current sales volume vs. budgeted sales. This mix effect, price and volume is displayed at the level of different categories and/or filters (Country, brand, product). When including the formula for calculation e.g. Price effect and select two countries at the time, the price effect shown to me is being calculated taking into account the total income of the countries / Total units of the two countries. Which is a very different result if you calculate the price effect of each country separately and add it up. (I include image with example).

What I'm needing is for my dashboard to still apply the included filters, respect me the price effect for each country and add it up instead of focusing it on total revenue and units.

I was told that this could be solved using the SUMX formula, yet I have not managed to fix it. In advance a thousand thanks for your collaboration with the concern!

Best regards.

[MFelix · Accepted solution]

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?

[EllieSim]

Hi Miguel,

Many thanks for your suggestion. There seems to be an issue with the search in the column. Otherwise, I adjusted the columns as in the attached and the syntax seems correct. Could you please have a look in case I have missis something? Many thanks!

[MFelix]

Hi @EllieSim

Looking at your code is difficult to get the correct answer without data. 

There is a category column correct?

Is it possible for you to send a file mockup by private message.