power query matching pattern

[vijaykaali811]

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 

[vijaykaali811]

it worked .   but with multiple wildcard in pattern . it will be difficult as number of wildcard is not predictable 

[vijaykaali811]

Lightening speed answer . thanks 

[Shravan133 · Accepted solution]

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