Excel & Sharepoint - feeding data from 3 workbooks to one master

Good afternoon,

I'm working on a project at work currently and I've hit somewhat of a brick wall,

I have managed to set up a master sheet that takes the data from 3 separate workbooks and transfers it into a master sheet. This master sheet then collates all 3 tables into one large table.
To do this I have used the Power query function on Excel.

However I've stumbled across a problem that when a user other than myself attempts to use this file it gives me an error.

It would appear that it's trying to either reference a point on my local drive (This was all created/worked on via sharepoint) or it's trying to reference my credentials on sharepoint.

Does anyone know how to get around this?

User should change the path to the files
PQ Editor - Home - Data source settings - Change source
then Refresh

Would that mean each user of the "Master sheet" would have to change source? the people using this sheet aren't exactly well versed with excel related work hence myself having to set this up
is there a better way to do it rather than using data query that involves formula's that can read from the closed sheet? or have I already used the best option?

I'd love to share the files to enable a better understanding of the issue, however it contains personal details which would probably be a breach of GDPR

the error that appears when another user opens is as followed [DataSourceEror] Could not find a part of the path 'C:\Users\my.name/business.name\sharepoint-area\folder\filename.xlsm'

In terms of accessing Sharepoint it's generally done by going to the sharepoint link - logging in using our work provided email addresses or by logging in and syncing the files first and then going from there via file explorer

I need to go so I'll leave you in a good hands of CK76

Originally Posted by CK76

It looks like your issue is that the path being used is your local folder (synched with SharePoint perhaps). Other users will not have access to that path.
They can set it to their own synched folder path.

But I'd recommend connecting to cloud Sharepoint folder (assuming SharePoint online).

Use Query from Web in PQ to connect to OneDrive/SharePoint (works the same way). Make sure you set up appropriate share policy on the folder/file.

Easiest way to get the path is to upload file to OneDrive, open, edit in Excel. Go to File menu in Excel, and right click on the file name. Copy path to clipboard.
Remove "?web=1" from the path.
Source should look like...

Please Login or Register  to view this content.

Then each user can use their own credential to login to MS account and query file(s).

This is probably me being stupid , but when I'm trying to get the data from web and I insert the link attained the way you have stated, it throws me an error saying " Details: "The input URL is invalid. Please provide a URL to the file path on SharePoint up to the file name only (with no query or fragment part)."" again I'm probably being stupid

Please don't quote entire post
Just the relevant part is better. Quoting entire post just creates clatter.

First, open file in SharePoint. Go to edit in Excel. Once it's open. You can go to File - > Info.

Click on OneDrive/SharePoint - path that just above "Protect Workbook". Copy to clipboard and paste to editor. Remove anything that appears after file name (usually "?web=1").

Then use basic mode in web query and paste in the path. It will automatically prompt you to login to MS account if you haven't already.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.