Excel Queries & connection / Power Query doesn't preserve hyperlinks-CodePudding

I created a Power query in order to extract some columns from a sheet of the same workbook. All the fields are correctly kept but the hyperlinks disappeared. How it can be fixed? are there any particular properties to flag? I've already flagged the property "Preserve cell formatting"

CodePudding user response：

Are you trying to read in some spreadsheet data and grab the embedded hyperlinks? I don't think PQ is able to do that yet. You can add a small VBA into the spreadsheet to create a custom function that would show the hyperlink, then powerquery could read that output

VBA:

Function GetURL(cell As Range)
    GetURL = cell.Hyperlinks(1).Address
End Function

then

= GetURL(cell)

CodePudding user response：

I found sort of a dirty solution. You can create a new custom column with a custom formula in PQ with the following text: "=WHATEVER_FORMULA_NAME("http://google.com")" Then you just replace WHATEVER_FORMULA_NAME with HYPERLINK and then Excel should treat the cells as you just entered the hyperlink formula, and the results should be working hyperlinks.