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?


    • 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


  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.


    • 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!


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


Leave a Reply