Pattern matching

[Kallagan]

Hello

I need your help. I'm having a problem with the pattern matching, or it's my formula, (I'm sure it's my formula)

I have 4 Excel sheets, 1 for each factory, that contains tickets extract like ticket number, subject type etc.

I have created a request to help dispatch each factory ticket to each support center.

TabKeys = Table.Buffer(Feuil2),
    Relative = Table.AddColumn(#"Changed Type1", "Support", 
            (Earlier) => Table.SelectRows(TabKeys, each Text.Contains(Earlier[Subject],[Pattern], Comparer.OrdinalIgnoreCase))
           ),
    ExpTab = Table.ExpandTableColumn(Relative, "Support", {"Support"}, {"Support.Center"})

This creates a new column that adds the "Support.Center"

I have created a new table with all the 4 sheets to create a dashboard (with union)

The pattern matching is done on the "Subject" column. Sometimes, the subject columns contains words that matchs multiple support center. So I have lines that are added.

In the end, I have more lines that I should have and the dashboard is false.

Is there a way to correct that ?

For ex. tell power bi something like

1 pattern = 1 line then move next ?

Thank you for help help

[v-shex-msft]

HI @Kallagan,

I'm not so sure for your table structure and previous steps and variables, it will be help if you share a pbix file and sample excel sheet.

After checked on your formual, I found below issues:

1, you missed the each Iterator of Table.Add Column function.

    Relative = Table.AddColumn(#"Changed Type1", "Support", 
          each (Earlier) => Table.SelectRows(TabKeys, each Text.Contains(Earlier[Subject],[Pattern], Comparer.OrdinalIgnoreCase))
           )

2. For Text.Contains function, it only allow string characters, what you invoked in function is a list.(I' m not so sure how you defene Earlier variable, I guess it is should be a table)

I'd like to suggest you add {index} to defind which item from list used to compare in function. Otherwise please use list.Contains functions to check value.

    Relative = Table.AddColumn(#"Changed Type1", "Support", 
          each (Earlier) => Table.SelectRows(TabKeys, each Text.Contains(Earlier[Subject]{0},[Pattern], Comparer.OrdinalIgnoreCase))
           )

    Relative = Table.AddColumn(#"Changed Type1", "Support", 
          each (Earlier) => Table.SelectRows(TabKeys, each List.Contains(Earlier[Subject],[Pattern]))
           )

Reference links:

Power Query Operators

Table.AddColumn	Adds a column named newColumnName to a table.
Text.Contains	Returns true if a text value substring was found within a text value string; otherwise, false.
List.Contains	Returns true if a value is found in a list.
List.ContainsAll	Returns true if all items in values are found in a list.
List.ContainsAny	Returns true if any item in values is found in a list.

Regards,

Xiaoxin Sheng

[Kallagan · Accepted solution]

Hello

I've tried with both formula, I've got an error like cannot tranform 1 in a Type value.

But I've managed to solve my problem with the formula bellow:

 Relative = Table.AddColumn(#"Changed Type1", "Support", 
            (Earlier) => Table.FirstN(
                Table.SelectRows(TabKeys, each Text.Contains(Earlier[Subject],[Pattern], Comparer.OrdinalIgnoreCase))
                ,1)
           ),
    ExpTab = Table.ExpandTableColumn(Relative, "Support", {"Support"}, {"Support.Center"})

Thank you anyway for your help.