Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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.g...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    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.

     

helassal's avatar
helassal
Resolver II
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    Ashish_Mathur
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Ashish_Mathur
        Super User
        6 years ago

        Hi,

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