Search code examples
hyperlinkconcatenationpowerquery

Power Query: Concatenate Text Columns Error

My intention is to use Power Query to create a hyperlink in the pivot table by adding the function "=HYPERLINK()" to the existing link (https://example.com) in the rows. However, I am already stuck at the point where Excel does not convert the text of the hyperlink function with the link text and displays an error.

Cell value (type: text): "https://example.com"

M Code:

AddHyperLink = Table.TransformColumns(TheStepBefore,{{"My Column", each "=HYPERLINK(""" & [#"My Column"] & """;" & """Source""" & ")", type text}})

This should resolve in the following string: "=HYPERLINK("https://example.com","Source")"

But instead I get the following error:

  • German: "Expression.Error: Der Feldzugriff kann nicht auf den Typ "Text" angewendet werden."
  • English translated: "Expression.Error: The field access cannot be applied to the type "Text"."

Screenshot of Error Message (Details just show the column name and cell value)

And when I just hardcode the Link and concatenate it, it works:

AddHyperLink = Table.TransformColumns(TheStepBefore,{{"My Column", each "=HYPERLINK(""" & "https://example.com" & """;" & """Source""" & ")", type text}})

Does anyone have some ideas why I cant concatenate a text value to another text value? Thanks!

Solution

  • Try

     AddHyperLink  = Table.TransformColumns(TheStepBefore,{{"My Column", each "=HYPERLINK(""" & _ & """;" & """Source""" & ")", type text}})