Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ScORE
Helper I
Helper I

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
v-chuncz-msft
Community Support
Community Support

@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
v-chuncz-msft
Community Support
Community Support

@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.
AlB
Super User
Super User

Hi @ScORE

 

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

Hi @AlB my apologies, I added the text.  

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];
        ", "
    )

 

   

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.  

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

@ScORE

Here is an example file with the code working. Have a look. 

This is how i have it entered:

Company =
VAR _SubsChar = UNICHAR (167)
VAR _SubsChar2 = UNICHAR (174)
VAR _Employees = TRIM ( Submittals[Approvers] )
VAR _NumberItems = 1 + LEN ( _Employees) - LEN ( SUBSTITUTE ( _Employees, ",", "" ) )
VAR _Res = _SubsChar & SUBSTITUTE (_Employees , ",", _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 )
),
"ApproverCompany",
VAR _ApproverCompany = LOOKUPVALUE(directory[Company], directory[Full Name],[SingleName])
RETURN
IF ( ISBLANK (_ApproverCompany), "**NOT FOUND**", _ApproverCompany)
),
[ApproverCompany],
", "
)

@ScORE

It probably has to do with the LOOKUPVALUES() finding more than one row with the name for the comedian in the NamesTable  

i changed it to you revised formula that uses nameswithdelimiters and now i get this error:  

 

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

2018-12-20_15-33-09.jpg

@ScORE

If you upload the pbix I might be able to have a look

Ok, I'll have to trim down a copy.  Theres sensitive data in it.  

Oh, okay, I changed them back to your numbers, but now I am getting an error.  These are lation unicodes 167 § and 174 ®; a section sign and a registered sign.  

@ScORE

What error?

 

A table of multiple values was supplied where a single value was expected

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

Yeah but it shouldn't throw that error.

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

 

@ScORE

the result should look like this

 

image.png

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors