Forum Discussion

Havrikahn's avatar
Havrikahn
Regular Visitor
8 years ago
Solved

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

 

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?

 

 

  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    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

     

6 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

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

      Thanks.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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

    • RR-Ak-Lex's avatar
      RR-Ak-Lex
      Regular Visitor
      4 years ago

      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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    This calculated column formula works fine as well

     

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