How Power Query can return clickable hyperlinks with friendly names to Excel

When you use Power Query as an Excel-automation-tool rather than just to feed the data model, you might want to return clickable hyperlinks that carry friendly names. This doesn’t work out of the box, but with a little tweak it will be fine:

The trick

Return a text-string that contains the Excel (!)-formula for hyperlinks, preceded by an apostrophe  ‘ . After the data has been loaded to the sheet, check the column and replace ‘= by = to activate your Excel-formula:

Activate the HYPERLINK formula by replacing ‘= with =

You can then format the column to “Hyperlink”:

Format as Hyperlinks

And make sure that future refreshes are automatically formatted as well:

Make sure format sticks

This of course works with all other excel-formulas as well. So if you’re still struggling a bit to calculate your numbers in Power Query, you can simply create a column that holds your Excel-formula which you “activate” by the replacement 🙂

The formula

This formula creates the new column, bringing in the content of columns “URL”  and “Friendly Name” to the Excel-formula:

Table.AddColumn(Source, “ExcelLink”, each “‘=HYPERLINK(“”” & [URL] & “””, “”” & [Friendly Name] & “””) “)

Quotation marks and escape signs

You’ll probably notice the multitude of quotation marks: Quotation marks are used in M to indicate that a text-element is following and will also be used at the end of a text-element. In that context, they are called “escape signs”. So what happens when your text ends with a quotation mark like here: ‘=HYPERLINK(” ? How to prevent the ending quotation mark to be mistaken as escape signs and returning: ‘=(HYPERLINK ( , so cut off the last quotation mark? You add 2 additional quotation marks: That will keep one of it in the text-string.

Subscribers  can download the file here: ClickableHyperlinks.xlsx

Enjoy & stay queryious 🙂

Comments (9) Write a comment

  1. Pingback: #Excel Super Links #149 – Shared by David Hager | Excel For You

  2. After completing the transforming it is a must to keep the original columns “URL” and “Friendly Name” in place. In case I removed either one in power query then the hyperlink will be invalid on the output table. I used to remove any column not required anymore in the power query edit mode but this is not the case, right?

    Reply

    • Hi Julian,
      I’m not sure I understand, but I’ve uploaded a new version where just the clickable hyperlinks are returned.
      In the previous version I put the result below the input table and Excel doesn’t play nice when you put 2 table objects below each other. Maybe that caused some problems.
      Cheers, Imke

      Reply

  3. I found I should take out the single quote beside the equal sign “=” first as shown below
    Table.AddColumn(Source, “ExcelLink”, each “=HYPERLINK(“””&[URL]&”””, “””& [Friendly Name] &”””) “)
    then double click any cell under ExcelLink column turning the formula to a text (Friendly Name). By doing so the hyperlink was clickable.

    Reply

    • Yes, I’ve worked with that version before as well, but encountered problems (it’s been a while, so I don’t remember what it was actually), so I presented the replacement option.
      But if it works, this will actually be more convenient I think – so thanks a lot for pointing this out Julian!

      Reply

  4. Please try to add one more record then refresh it or simply refresh the replaced file then the issue will happen again.

    Reply

Leave a Reply