Matching values in one table, using wildcards, with string values in another table

[Havrikahn]

I have a large table (Table 1) containing a column with application names. These application names contain version numbers, etc. I would like to simplify this list of application names into groups of predefined strings.

I have created a seperate table (Table 2) with these predefined string values. The plan is to match each row in the application names column with the corresponding simplified string from Table 2. The matching is done by finding the simplified string within the application name.

I managed to create an excel formula for this, but cannot find a way how to use the "reverse wildcard" lookup method in Power BI.

Is this possible to do something similar in Power BI, or do I need to format the data before I pull it into Power BI?

[Ashish_Mathur]

Hi,

This calculated column formula works fine as well

=FIRSTNONBLANK(FILTER(VALUES(keywords[Keywords]),SEARCH(keywords[Keywords],Applications[Application],1,0)),1)

[Phil_Seamark · Accepted solution]

Hi @Havrikahn

This calculated column should work.

New Column = 
VAR Matches = 
    CALCULATETABLE(
        GENERATE(
            'Table1',
            FILTER(
                Predefined,
                SEARCH(
                    [Apps],
                    [Applications],
                    1,
                    0
                   )
                   >0)
             )
          )
RETURN 
    CONCATENATEX(
        Matches,
        [Apps],",")

Here is a PBIX file you can download to test.  This will also find and append when it discovers more than one match.

https://1drv.ms/u/s!AtDlC2rep7a-oi8WmrfnWoH031de

[RR-Ak-Lex]

Very nice Solution!

Is there a way to filter more fitting matches to the Search?

Like I have

Predefined:

"Apple"

"Apple TV"

And now in the Applications, I have:

"Apple TV Remote" 

It shows me:

"Apple, Apple TV" as match, but I only want the "better" match.

[Anonymous]

Would you please explain more about your solution, especially why column can be used as first argument of SEARCH?

Thanks.

[Anonymous]

Hi Hongjyan,

It's possible because you're pulling the entire column into the formula when you set the VALUES() term. This means you can also pull a different resultant column, like if you wanted to pull the vendor's name from the Keywords table based on matching the keywords[Keywords] column with Applications[Application] with a minor tweak; setting VALUES() to pull the entire table, then using SELECTCOLUMNS() to choose the field that you want to return:

VendorName = FIRSTNONBLANK(SELECTCOLUMNS(FILTER(VALUES(keywords),SEARCH(keywords[Keywords],Applications[Application],1,0)),"VendorName",[VendorName]),1)

Hope this helps,

David

[Havrikahn]

Hi @Phil_Seamark, this solution worked great. Thank you!