Power Query output table link back to original data

[HWillis]

Hello,

I am wondering if there is a way to hyperlink (or similar) back to the original data from a Power Query.

I have a Workbook with 'data' tab and a power query to extract only some required info from this table in the same workbook.

When someone is looking at the report produced by the Power Query - is there a way to click a record in the report to go back to the parent data and update the original record?

Thanks in advance for any advice.

Cheers, H.

 

[GraH]

Hi HWillis,

You can add a column in the table in Excel that's made by the PQ and add a hyperlink formula. That one will depend on the data at hand.
For example, given there is a Record ID like [RecID] (which can be made with "Index" in PQ, in my case it started at row 2), then something similar to this formula might do the trick:
=HYPERLINK("#'OrginalDataSheetName'!A"&[@RecID],HyperlinkFriendlyText)), where HyperlinkFriendlyText refers to the column or a formula containing the text the hyperlink needs to show.

 

[HWillis]

Hi HWillis,

You can add a column in the table in Excel that's made by the PQ and add a hyperlink formula. That one will depend on the data at hand.
For example, given there is a Record ID like [RecID] (which can be made with "Index" in PQ, in my case it started at row 2), then something similar to this formula might do the trick:
=HYPERLINK("#'OrginalDataSheetName'!A"&[@RecID],HyperlinkFriendlyText)), where HyperlinkFriendlyText refers to the column or a formula containing the text the hyperlink needs to show.

Thank you for your advice - I will give this a go.
@GraH - you put me onto Power Query a few weeks ago - has been an amazing discovery for me - thanks
Have a great day

 

[GraH]

@GraH - you put me onto Power Query a few weeks ago - has been an amazing discovery for me - thanks
Have a great day

Haha, that's just great feedback. Mission accomplished: more power (query) to the people , or is it the inverse?
You too, thanks.

 

[HWillis]

Thank you for your advice - I will give this a go.
@GraH - you put me onto Power Query a few weeks ago - has been an amazing discovery for me - thanks
Have a great day

Hi G - sorry I have been trying to work this out - and am a little confused - do I add the column with the formula in the excel table or am I doing this in power query?

I have done a small test.

I have this code in my table on sheet 1....... =HYPERLINK("#'Sheet1'!Table1[ID]","click") which works.

The power query returns

how do I get it to just be "click"?

Once I hit enter in the formula bar "click" appears and the link works back to sheet 1 - but I can't do that for every row.

Many thanks, H.

 

[GraH]

Hi,
you add this in the Excel table in Excel generated by PQ, not directly in PQ.
Make sure not to format your cell as text, but keep it general (it might be the reason why the full text shows up, instead of the formula result, which should be "click".
Since it is a table, the formula should be automatically filled out in the full column.
I think there is small error in the formula try =HYPERLINK("#'Sheet1'!A"&Table1[ID],"click"), given your ID is the row number of the task in the original data.

 

[HWillis]

Hi,
you add this in the Excel table in Excel generated by PQ, not directly in PQ.
Make sure not to format your cell as text, but keep it general (it might be the reason why the full text shows up, instead of the formula result, which should be "click".
Since it is a table, the formula should be automatically filled out in the full column.
I think there is small error in the formula try =HYPERLINK("#'Sheet1'!A"&Table1[ID],"click"), given your ID is the row number of the task in the original data.

I have it now - woohoo - I left it all day and came back to it.
I found that the forumla I used actually goes to the ID number. The formula you gave me works if you generate an index and goes to the row number.
Thanks so much.
H.

 

[HWillis]

........... update just tried in my working doc - it won't work through Sharepoint ?

 

[GraH]

Oh ****'... Also when you refer to the full path of the file?
I have no experience with Sharepoint so it's a shot in the dark, but one must try.

Edit. Haha, that's so American, replacing harmless swear words with "*". ?

 

[HWillis]

Oh ****'... Also when you refer to the full path of the file?
I have no experience with Sharepoint so it's a shot in the dark, but one must try.

Edit. Haha, that's so American, replacing harmless swear words with "*". ?

hmmm will have to try anything - as it is such a useful thing - and looking back was really quite easy to get the end result.......... thanks.