cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ScORE Regular Visitor
Regular Visitor

search text string in a column and see if it matches a value in a different tables column

I need help searcing searching a text string in a column to see if it matches a value in a different tables column.  The tables are not connected.  If the text string contains the value, I want to return another value into a new column.  Sample tables: 

 Table 1                                                                                             Table 2

Full NameCompany Comeidans on set Sets today
John StewartLaugh out loud John Stewart, Kevin Hart1
Terry FatorFunny Business Terry Fator, Ricky Gervais2
Larry DavidLaugh until your stomach hurts Larry David, Chevy Chase3
Gary ChandlerUS laughs Gary Chandler, Jay Leno4
Jim GaffinElectric comedy Jim Gaffin, David Letterman5
Dave ChappelleComedic Comedy Dave Chappelle, Adam Sandler6
Jerry SeinfeldFunny Guy Jerry Seinfeld, Vill Murray7

 

 

comedy.jpg

Basically, I want too search/look in the comedians on set column, for a match to one the names in my full names column and return the company in a column in the table with comedians on set as a value.  example:

 

seach comedians on set line 1 find john stewart as a match and return Laugh out loud as avalue in a new column in my comedians on set table.  

 

Your help is greatly appreciated!!!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: search text string in a column and see if it matches a value in a different tables column

@ScORE,

 

You may use DAX below to add a calculated column.

Column =
VAR d = ", "
RETURN
    CONCATENATEX (
        FILTER (
            Table1,
            SEARCH ( d & Table1[Full Name] & d, d & Table2[Comeidans on set] & d, 1, 0 )
                > 0
        ),
        Table1[Company],
        d
    )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

19 REPLIES 19
Super User
Super User

Re: search text string in a column and see if it matches a value in a different tables column

Hi @ScORE

 

Can you post the sample data in text format rather than on a screen capture? (so that it can be readily copied)

ScORE Regular Visitor
Regular Visitor

Re: search text string in a column and see if it matches a value in a different tables column

Hi @AlB my apologies, I added the text.  

Super User
Super User

Re: search text string in a column and see if it matches a value in a different tables column

Hi @ScORE

I came up with something that might be overly complicated but hopefully useful.

I've used the names 'NamesTable' and 'SetsTable' for what you called Table1 and Table2. Note that I've also updated the name of the first column of 'SetsTable' to 'Comedians on set' from the 'Comeidans on set' that you had. I'm saying just to spare you "Columns not found" errors.

The code should work regardless of the number of names that you have on 'Comedians on set'. That is why it got a bit long. We are creating a new calculated column, SetsTable[Company], that will show the company names for each comedian, in the same order as the comedians appear, separated by ", ". If a company is not found, the code will return "**NOT FOUND**". You can update all this in the code as required.

 

Let me know if this helps.

 

 

'SetsTable'[Company] = 
VAR _SubsChar = UNICHAR ( 167 )
VAR _SubsChar2 = UNICHAR ( 174 )
VAR _ComediansNames = TRIM ( SetsTable[Comedians on set] )
VAR _NumberItems = 1 + LEN ( _ComediansNames ) - LEN ( SUBSTITUTE ( _ComediansNames; ","; "" ) )
VAR _Res = _SubsChar & SUBSTITUTE ( _ComediansNames; ", "; _SubsChar ) & _SubsChar
VAR _Table1 = GENERATESERIES ( 1; _NumberItems )
RETURN
    CONCATENATEX (
        ADDCOLUMNS (
            ADDCOLUMNS (
                _Table1;
                "SingleName";
                VAR _Start = FIND ( _SubsChar2; SUBSTITUTE ( _Res; _SubsChar; _SubsChar2; [Value] ) ) + 1
                VAR _Ending = FIND ( _SubsChar2; SUBSTITUTE ( _Res; _SubsChar; _SubsChar2; [Value] + 1 ) ) - 1
                VAR _NumChars = _Ending - _Start + 1
                RETURN
                    MID ( _Res; _Start; _NumChars )
            );
            "ComedianCompany";
            VAR _ComedianCompany = LOOKUPVALUE ( NamesTable[Company]; NamesTable[Full Name]; [SingleName] )
            RETURN
                IF ( ISBLANK ( _ComedianCompany ); "**NOT FOUND**"; _ComedianCompany )
        );
        [ComedianCompany];
        ", "
    )

 

   

Super User
Super User

Re: search text string in a column and see if it matches a value in a different tables column

@ScORE

the result should look like this

 

image.png

Super User
Super User

Re: search text string in a column and see if it matches a value in a different tables column

@ScORE

by the way, make sure sure you have the latest version of Power BI installed. I believe the LOOKUPVALUE behaviour has changed slightly recently (it now returns a blank instead of an error when there is no match) and the code assumes that new behaviour.

ScORE Regular Visitor
Regular Visitor

Re: search text string in a column and see if it matches a value in a different tables column

Thanks for the response.  I had to make a some changes because i was getting abunch of error lines. I replaced all the ";"'s with ","'s.  and removed all ther error lines.  However, now I'm getting an error that says:

The Search Text provided to function 'FIND' could not be found in the given text.   

 

Do you know what could be causing this?  

ScORE Regular Visitor
Regular Visitor

Re: search text string in a column and see if it matches a value in a different tables column

I also corrected the UNICHAR codes to represent  a comma & space sign.  That seemed to have corrected the issue. However, now I am getting the following error:  an argument of function 'MID' has the wrong data type or has an invalid use.  

Super User
Super User

Re: search text string in a column and see if it matches a value in a different tables column

@ScORE

Yeah but it shouldn't throw that error.

The ";" or the  ", " as code delimiters depends on the locale settings of your machine

 

Super User
Super User

Re: search text string in a column and see if it matches a value in a different tables column

@ScORE

 


@ScORE wrote:

I also corrected the UNICHAR codes to represent  a comma & space sign.  That seemed to have corrected the issue. However, now I am getting the following error:  an argument of function 'MID' has the wrong data type or has an invalid use.  


You should not  do that. The UNICHAR codes are for the substitute function and are used as delimiters. To make things easier wwhen extracting th names.  That is why they have to be "weird" characters that won't come up in normal text. You cannot change them, certainly not for comma and space.  

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 51 members 1,205 guests
Please welcome our newest community members: