Forum Discussion

Kallagan's avatar
Kallagan
Frequent Visitor
8 years ago
Solved

Pattern matching

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

Need Help
  • Kallagan's avatar
    Kallagan
    8 years ago

     

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      Kallagan
      Frequent Visitor
      8 years ago

       

      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.