Forum Discussion
Pattern matching
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.
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:
| 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
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.