Extract originating cell reference of consolidated worksheets

[henryg]

I have a PQ that consolidates various worksheets, and on that "Summary" added a column with the originating table names. All good so far other than PQ imports table names containing '-' with '_', hence the "Replaced Underscores" code.

I would also like the originating worksheet cell reference in a column but can't get it to work that even using AIs. The M code is below. Anything I can do?

let
Source = Excel.CurrentWorkbook(),
#"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "hFirst") or Text.StartsWith([Name], "Joint")),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Name", Order.Ascending}}),
#"Expanded Content" = Table.ExpandTableColumn(#"Sorted Rows", "Content", {"Date", "Description", "Amount", "Notes"}, {"Date", "Description", "Amount", "Notes"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Content",{{"Date", type date}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type", each true),
#"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each ([Date] <> null) and ([Description] <> "Opening balance")),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows2",{{"Name", "Table Name"}}),
#"Replaced Underscores" = Table.TransformColumns(#"Renamed Columns", {"Table Name", each Text.Replace(_, "_", "-"), type text})
in
#"Replaced Underscores"

Ideally I would like to have hypertext links to the originating data.

 

[henryg]

After much trial and error and messing in PQ, and finally in Excel I get the originating cell location, by:

Formula: =TEXTAFTER(CELL("address",XLOOKUP(C4,INDIRECT(E4&"[Amount]"),INDIRECT(E4&"[Amount]"))),"]",1)

Result: h_1999'!$C$6

h_1999 is the worksheet name (although it is also a table name).

How/can this become a live hyperlink? There are over 8000 rows so it needs to be via a formula or vba