power query matching pattern

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