Add Hyperlink to a cell column in Power Query

[davidleal]

I am trying to follow the steps indicated in the following link: How Power Query can return clickable hyperlinks with friendly names to Excel to return a clickable column in Excel with the corresponding hyperlink. This is a simpe sample based on my real scenario. I have the following Excel table:

and I want to return a clickable cell for Key and Epic Link. Starting with the first column (Key), I duplicate it and add the URL as a prefix, so I can have in column Key the friendly name and in column KeyURL the URL. The line that does the magic from the referred website gave me a syntax error:

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

but the following works for me:

#"add KeyLink" = Table.AddColumn(#"Added Prefix", "Key Link", each "'=HYPERLINK([@KeyURL], [@Key])")

here is the entire M-Sript:

let
  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", type text}, {"Summary", type text}, {"Epic Link", type text}}),
  #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Key", "Key - Copy"),
  #"Renamed Columns" = Table.RenameColumns(#"Duplicated Column",{{"Key - Copy", "KeyURL"}}),
  #"Added Prefix" = Table.TransformColumns(#"Renamed Columns", {{"KeyURL", each "https://mysite.atlassian.net/browse/" & _, type text}}),
  #"add KeyLink" = Table.AddColumn(#"Added Prefix", "Key Link", each "'=HYPERLINK([@KeyURL], [@Key])")
in
  #"add KeyLink"

at the end I get the following in Power Query:

and here is the output in Excel:

but at the end it requires a manual intervention to replace the aposthrope (')  with an empty string, so the result would be:

If I understood it correctly it works, but it requires a manual intervention all the time, unless I didn't understand the approach and I am doing something wrong. Is there a way to achieve it without this manual final step? I was thinking to use Office Script to replace this final steps, but it is too much for just adding a link to a column. The referred post is from 2017, so I am wondering if since then there is better way of doing it. Thanks

I know in Power BI it is out of the box, but I don't have it, I can only use Power Query.

Thanks for any help,

David

[v-xinruzhu-msft]

Hi @davidleal 

You can refer to the following link.

excel - PowerQuery use a cell values in a URL - Stack Overflow

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.