Forum Discussion
power query matching pattern
i have table1 with column title column another table2 with column pattern and category
table1.title
"job xx run on"
"password for id xxx expired"
table2
pattern category
job*run Jobrun
password*expired passwordexpiry
with this now i want to get category in table1 with values table1.title matching table2.pattern , get table2.category
I did this in excel with just xlookup and match.
But how to do that in Powerquery power bi
Add Custom Column to Match Patterns
In Table1, add a custom column that looks through each Pattern in Table2, checks if it matches using a basic wildcard logic (we'll treat * as "any characters"), and returns the corresponding Category.
Go to Add Column > Custom Column in Table1 and paste:
let
currentTitle = [Title],
matchedCategory =
List.First(
List.Transform(
Table.SelectRows(Table2, each
Text.Contains(currentTitle, Text.BeforeDelimiter([Pattern], "*"))
and Text.Contains(currentTitle, Text.AfterDelimiter([Pattern], "*"))
)[Category],
each _
),
null
)
in
matchedCategory
This assumes your Pattern always has a single * (wildcard) in the middle.
What this does:
- For each row in Table1, it:
- Loops through all Patterns in Table2
- Breaks each pattern at the * into two parts
- Checks that the Title contains both parts
- Returns the first matching Category
Example Result
Title
Category
job xx run on
Jobrun
password for id xxx expired
passwordexpiry
3 Replies
Add Custom Column to Match Patterns
In Table1, add a custom column that looks through each Pattern in Table2, checks if it matches using a basic wildcard logic (we'll treat * as "any characters"), and returns the corresponding Category.
Go to Add Column > Custom Column in Table1 and paste:
let
currentTitle = [Title],
matchedCategory =
List.First(
List.Transform(
Table.SelectRows(Table2, each
Text.Contains(currentTitle, Text.BeforeDelimiter([Pattern], "*"))
and Text.Contains(currentTitle, Text.AfterDelimiter([Pattern], "*"))
)[Category],
each _
),
null
)
in
matchedCategory
This assumes your Pattern always has a single * (wildcard) in the middle.
What this does:
- For each row in Table1, it:
- Loops through all Patterns in Table2
- Breaks each pattern at the * into two parts
- Checks that the Title contains both parts
- Returns the first matching Category
Example Result
Title
Category
job xx run on
Jobrun
password for id xxx expired
passwordexpiry
Lightening speed answer . thanks
it worked . but with multiple wildcard in pattern . it will be difficult as number of wildcard is not predictable
