Hi. Interesting question here.
I'm combining some audit files (located in Sharepoint Online as excel files) with Power Query. The audit files contain audit items that need remediated. There is a separate excel file that has dozens of sheets. Each sheet contains a list of the objects that need remediated for each corresponding audit item in the first spreadsheet i mentioned. I need to build a custom column that embeds a link that opens a URL to the spreadsheet that contains the sheets with the objects to remediate AND opens the spreadsheet with the appropriate sheet displayed. How can I accomplish this? The kicker is, the remediation spreadsheet changes every month so it is not a static file....so I need Power Query to dynamically build the URL upon every refresh.
Like this - but it's opening another spreadsheet on Sharepoint and opening the spreadsheet to the tab named "98 AbnormalPasswordRefresh".