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 🙂
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
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!
Please try to add one more record then refresh it or simply refresh the replaced file then the issue will happen again.
Hi Julian,
not sure which issue you exactly mean, but my methods works fine if I add more records (while yours doesn’t unfortunately).
Never mind, it’s not a big issue. Thanks a lot.
You’re always welcome Julian!
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
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] & “””) “
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.
Yes, you have to do the replacement after every refresh (but not the formatting of the column in the table).
every time…brilliant
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/
Thanks for the tips Ben!
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?
Hi Eric,
in Power BI its much easier: https://docs.microsoft.com/en-us/power-bi/power-bi-hyperlinks-in-tables
Cheers, Imke
Hello lmke,
Thank you for the quick response! Unfortunately I don’t think I was specific enough in my request. I’m aware that you can create URL’s and URL icons in Power BI. What I’m hoping to do is create friendly names for URL’s (clickable text) instead of showing the full URL or an icon. Is that possible?
Oh I see. Unfortunately I cannot think of a way to do so.
/Imke
Pingback: Integration Candidate Analysis: Notes from the Sausage Factory – Enterprise Data Foundation
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
Thanks this was useful
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.
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?
Sorry Nicolai,
but no idea here.
/Imke
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. 🙁
Please mark in the header of your post that search&replace has to be done on every data refresh
I used the given formula for the hyperlink with length exceeding 255 characters in a cell. The output is showing the SPILL error. How to resolve the issue?