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 (27) Write a comment

  1. 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

  2. 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

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

    Reply

  4. HI,
    Sorry for the basic question…. but..
    How can I enter this exactly?

    I add a new column then entering Table.AddColumn(Source, “ExcelLink”, gives an “Invalid identifier” error.

    thank you

    Reply

    • That’s actually the full code generated by the expression. In your case, you just fill in this part of the formula then:
      “‘=HYPERLINK(“”” & [URL] & “””, “”” & [Friendly Name] & “””) “

      Reply

  5. Thanks for a useful post. It’s not working 100% for me, but perhaps I misunderstood. When you state ‘After the data has been loaded to the sheet, check the column and replace ‘= by = to activate your Excel-formula:’ , do you mean that this must be done every time the data is refreshed, or does excel ‘remember’ this trick (for example as part of the ‘cell formatting’ which is persisted by the checkbox)? In my case, the hyperlink formatting is all that is kept – I have to do ‘the trick’ each time the data is refreshed.

    Reply

  6. Thanks for sharing this concept with the annotated images. I think you should have more clearly stated that the Find/Replace operation has to be repeated with each refresh and is not included in the “Preserve cell formatting”.

    It stinks that WordPress converts code snippets to Smart Quotes. Are you able to control that? I found this article that may help:
    https://www.ironpaper.com/webintel/articles/fix-wordpress-quotes-by-displaying-straight-quotation-marks/

    Reply

  7. I know it has been a while since this post was updated but I wonder if you are able to accomplish something similar in Power BI Desktop vs. the excel version?

    Reply

  8. Pingback: Integration Candidate Analysis: Notes from the Sausage Factory – Enterprise Data Foundation

  9. This is how I do it…
    Private Sub Worksheet_TableUpdate(ByVal Target As TableObject)

    Dim cell As Range
    For Each cell In Range("Query1[two]").Cells
    cellContentSplit = Split(cell, "|") '''Pipe used as delimiter
    ActiveSheet.Hyperlinks.Add _
    Anchor:=cell, _
    Address:=cellContentSplit(1), _
    TextToDisplay:=cellContentSplit(0)
    Next

    End Sub

    Reply

  10. Couldn’t a little macro triggered by the refresh do the find and replace? Or have a macro button that runs the refresh and then does the find and replace. I’m sure there must be an issue with that as it seems too obvious for you not to have thought of it.

    Reply

  11. Hi, Thanks for sharing the idea Imke, In O365 2016 whenever I do a ‘Find and Replace’ for ‘= to = Excel throws the error > Cannot enter a formula for an item or field name in a PivotTable report – any idea how to get around this please?

    Reply

  12. In Tutorial code examples, please always use plain text. The text you used for the example cannot be copy/pasted and edited because the quote marks are weird and Excel doesn’t recognize them. 🙁

    Reply

  13. Please mark in the header of your post that search&replace has to be done on every data refresh

    Reply

Leave a Reply