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 ...
  • 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

     

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

 

  • Havrikahn's avatar
    Havrikahn
    Regular Visitor
    8 years ago

    Hi Phil_Seamark, this solution worked great. Thank you!

  • 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.