Context: I recently accepted a new job and I'm training my replacement. I've built a lot of reports for the company and a majority of them have been saved to my OneDrive. I've been moving these files over to a SharePoint site the trainee can use which has required me to fix the power queries in my reports so that when she refreshes the links in the workbook that it pulls in data from another workbook present on the same sharepoint.

Problem: I'm using the "Add shortcut to OneDrive" feature from SharePoint so I can access the files from my file explorer, so when I go to select the file it builds a path from my username, preventing her from being able to refresh the workbook since the file path is inaccessable.

Steps I've Taken: I read this article

Then, in the workbook, click File > Info > Copy Path. (This will get you the path you need for power query to directly retrieve the workbook. It is different than what you would have gotten if you tried to copy the path using SharePoint's features for copying the path.)

Paste that path somewhere safe for reuse. Then close your workbook and open Power Query.

In Power Query, create two new parameters:

Click Home > the bottom part of the Manage Parameters button > New Parameters:

Change the name from Parameter1 to path and change the Type to Text, then paste the path you copied for your workbook into the Current Value box. Then, from the path you just pasted, delete all the text that follows the last forward slash (/). Make sure you do include that last / in the path. You'll put the file name that follows the / in a separate parameter and won't need the ?web=1.

Don't click OK. Instead, click New, at the top of the Manage Parameters window. This will set you up to create the filename parameter.

Change the name from Parameter1 to filename and change the Type to Text, then paste the filename you copied for your workbook into the Current Value box. Again, you won't need the ?web=1 from the original path.

Click OK.

Now you can use the parameters, path and filename in your source line. And here's how to do that:

Source = Excel.Workbook(Web.Contents(path & filename), null, true)

This will give you a table like this:

CodePudding user response：

There is a SharePoint connector for files native to Power Query, that will not use your username.

https://docs.microsoft.com/en-us/power-query/connectors/sharepointfolder

Side note, you cannot use CELL function because

• in older Excel, user may need to press CTRL ALT F9 to update the calculation with their username, user will hate it.
• in O365 Excel, it returns the file url if the file is stored in SP. So you will end up using the SharePoint connector above as well.