Forum Discussion

Wise1's avatar
Wise1
Icon for Helper I rankHelper I
9 years ago

Extract URL of hyperlink text - Suggestions

 

Hello All, 

 

I have been trying to find info on how to extract a "URL" of column data which Power Bi has imported off a website table that has clickable links in it. 

ie:  https://www.extendoffice.com/documents/excel/859-excel-list-hyperlinks.htm

 

The main aim is to have the URL exported and placed into another column and maintain the origional cell data. 

ie: 

 

ColumnData (From website)      |       URL Data

google (<--- URL)                      |    www.google.com.au

 

if google had a href url enabled as part of its data, can i have both the URL and the name "google" imported into PowerBi Desktop? 

 

 

 

Any pointers are greatly welcome! 

 

Thanks!

 

Need Help

3 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi Wise1,

     

    Take this link https://www.extendoffice.com/documents/excel/859-excel-list-hyperlinks.htm as an example, your expect result is displaying "www.extendoffice.com" and "extendoffice" in two new columns, right?

     

    Below is my test. I created a table named "URL" containing one column [ColumnData]. Then, I created two calculated columns based on [ColumnData].

     

    URLData =
MID (
    'URL'[ColumnData],
    FIND ( "http://", 'URL'[ColumnData] ) + 7,
    FIND (
        "/",
        MID (
            'URL'[ColumnData],
            FIND ( "http://", 'URL'[ColumnData] ) + 7,
            LEN ( 'URL'[ColumnData] ) - 7
        )
    )
        - 1
)

URLName =
MID (
    MID (
        'URL'[URLData],
        FIND ( ".", 'URL'[URLData], 1 ) + 1,
        LEN ( 'URL'[URLData] ) - FIND ( ".", 'URL'[URLData], 1 )
    ),
    1,
    FIND (
        ".",
        MID (
            'URL'[URLData],
            FIND ( ".", 'URL'[URLData], 1 ) + 1,
            LEN ( 'URL'[URLData] ) - FIND ( ".", 'URL'[URLData], 1 )
        ),
        1
    )
        - 1
)

     

     

    Alternatively, you can directly split this column into multiple columns then delete unnecessary columns. Go to Editor>Transform>Split Column>By Delimiter>Custom>"/". Reference: Extract url from text in DAX

     

    Thanks,
    Yuliana Gu

    • Wise1's avatar
      Wise1
      Icon for Helper I rankHelper I
      9 years ago

      Sorry i have not written back yet, i have been under the pump with Xmas coming up 

      - Thanks for taking the time to reply 

       

      i will have a look at this as soon as i can and let you know! 

       

      Thanks again for all the pointers and info and i will let you know asap !

    • dpasha's avatar
      dpasha
      Frequent Visitor
      9 years ago

      Hi Yuliana Gu,

       

      I have a similar scenario wherein i need to extract all the links from a given mail body. Right now i am able to extract only the first link or only link. Is there any possiblity to extract all the links and store it in a column.

       

      Any help would be greatly appreciated.

       

      Thanks

      Danish