cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mellyVK Member
Member

partial match url using search function

hi all

i am trying to do a partial match of url,similar to vlookup in excel.
any idea how to do it in powerbi?

 

myurltable
urlsource
http://abc.com
http://www.search.zzz.com
www.global.mmm.com.au/about us

 

mydata 
urlDomain
abc.comABC
zzz.comZZZ
mmm.comMMM

 

any advice is appreciate. Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: partial match url using search function

Hi,

This calculated column formula in the url-list Table works

Column = LOOKUPVALUE(urltable[Domain],urltable[url],FIRSTNONBLANK(FILTER(VALUES(urltable[url]),SEARCH(urltable[url],'url-list'[URL],1,0)),1))

Hope this helps.

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

6 REPLIES 6
helassal Regular Visitor
Regular Visitor

Re: partial match url using search function

Hi @mellyVK ,

 

I am afraid I am not able to follow, if possible, can you please add a descriptive statement of what you are trying to achieve, expected result and current result ? that will help us help you faster.

PaulDBrown Senior Member
Senior Member

Re: partial match url using search function

@mellyVK 

 

This is the sort of thing Power Query is very good at: extracting segments from strings using delimiters.

Can' you do it in in Power Query?

mellyVK Member
Member

Re: partial match url using search function

@helassal 
hi, i am trying to do similar to a vlookup in powerbi, can it return the value of "ABC" instead of "ZZZ"? 

zzz.JPG123.JPG

Thank you.

Super User
Super User

Re: partial match url using search function

Hi,

This calculated column formula in the url-list Table works

Column = LOOKUPVALUE(urltable[Domain],urltable[url],FIRSTNONBLANK(FILTER(VALUES(urltable[url]),SEARCH(urltable[url],'url-list'[URL],1,0)),1))

Hope this helps.

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

mellyVK Member
Member

Re: partial match url using search function

@Ashish_Mathur 

i got this error when i applied to the 10000+ of messy urls i had...

is there any way to avoid the errors?

qqq.JPG

Super User
Super User

Re: partial match url using search function

Hi,

This means that there are duplicates appearing in the URL column of the urltable Table.  Remove those duplicates.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,140)