cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mspreng Frequent Visitor
Frequent Visitor

Lookup table in combination with searching a text string

Long time lurker, first post, hi!

 

I have a table with some billing data and an ID. The ID column has the format xxx/[name]/xxx... OR xxx/yyy/zzz/[name] and the [name]'s in this ID string may or may not have a prefix or suffix.

 

I also have a table of "base" names that I want to search for in the ID so that I can slice and sum based on these names.

 

I'm trying to create a column where any ID that has an occurance of any of the Names from the Name list would just copy that Name to the new column.

 

This is the if statement that works for just one, but I need to be able to search each ID for all of the potential names and then use that name in the new column.
Base Name = if(search("abcd", 'Table1'[ID], 1, 0) > 0,"abcd","")

 

Example of results:

 

Table 1

ID                                           base name (new calc column)

xxx/abcd/yyy                          abcd

x/abcd-suffix/zzz/a                abcd

yyy/x/zzz/efgh                       efgh

 

Table 2

Names

abcd

efgh

etc

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Anonymous
Not applicable

Re: Lookup table in combination with searching a text string

Hey @mspreng

 

Your situations seems pretty interesting, but I've done a video on something very similar to this that I think might help you out. I don't have the exact link right now but if you go to youtube.com/c/bielite you can search for the "VLOOKUP" video I did a couple weeks back. The gist is to use the CONCATENATEX function to loop through all of your potential names and then the SEARCH function like you have already done.

 

Hope this helps,

Parker

3 REPLIES 3
Anonymous
Not applicable

Re: Lookup table in combination with searching a text string

Hey @mspreng

 

Your situations seems pretty interesting, but I've done a video on something very similar to this that I think might help you out. I don't have the exact link right now but if you go to youtube.com/c/bielite you can search for the "VLOOKUP" video I did a couple weeks back. The gist is to use the CONCATENATEX function to loop through all of your potential names and then the SEARCH function like you have already done.

 

Hope this helps,

Parker

mspreng Frequent Visitor
Frequent Visitor

Re: Lookup table in combination with searching a text string

Parker, this was EXACTLY it. Thank you for the well done and very clear video. I also had not been able to find a clear writeup on this anywhere. I will definitely be coming back to your channel for more tutorials.

 

Cheers!

 

Direct link to Parker's video: https://www.youtube.com/watch?v=_bdHe3z_fVU

skocheta Regular Visitor
Regular Visitor

Re: Lookup table in combination with searching a text string

Hello @Anonymous,

Thanks a lot for the video , That's precisely what I was looking for as well.

I have a scenario a bit different. We would like to use a dynamic table (Resultset from a selectcolumn + Search) as the source table rather than the static tabel in example.

 

Do you think that'd be possible ?

 

PS: We are going to use this pattern an Defining Roles for Row Level Security.

Detailed Query: https://community.powerbi.com/t5/Desktop/Lookup-table-in-combination-with-searching-a-text-string/m-p/496521#M231556