Extract URL of hyperlink text - Suggestions

[Wise1]

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!

[v-yulgu-msft]

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

[dpasha]

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

[Wise1]

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 !