partial match url using search function

[Anonymous]

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
url	Domain
abc.com	ABC
zzz.com	ZZZ
mmm.com	MMM

any advice is appreciate. Thank you.

[PaulDBrown]

@Anonymous 

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?

[helassal]

Hi @Anonymous ,

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.

[Anonymous]

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

Thank you.

[Ashish_Mathur · Accepted solution]

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.

[Anonymous]

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

[Ashish_Mathur]

Hi,

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