Holly has attended:
PowerPoint Intermediate Advanced course
Power Automate Introduction course
Excel Power Query Sharepoint Connection
Hi,
I have set up a power query connection that comprises of two sources. One is a connection to Dynamics Business Central vie ODATA feed and the other linking to an Excel workbook (located on SharePoint). The problem with the Excel workbook is that when a colleague try's to open the power query the file location seems associate to my own credentials as opposed to generically the MS SharePoint and therefore needs re-pointing. Is there a way to overcome this please? See the connection path below I'm getting when simply selecting excel as a data source in Power Query.
= Excel.Workbook(File.Contents("C:\Users\hollyaffinito\The Really Useful Group Ltd\Finance - Documents\Monthly Mgmt Accts\FY2025\Accruals Checker\Finance Calendar.xlsx"), null, true)
RE: Excel Power Query Sharepoint Connection
Hi Holly
Thankyou for your question to the forum
The issue you have is that the file path connection is based on your local Excel file not the one on the Sharepoint site. To connect to an Excel file in SharePoint and make it available to your colleague without the need for repointing, you need to do the following:
1. Open SharePoint and open the Excel file. Then, open the Excel file in the Excel desktop app
2. In the Excel desktop app, go to File - Info
3. Click Copy Path
4. Open Notepad and paste the link
5. Delete the last bit of the link which reads ?Web=1
6. Select and copy the link
7. Open a blank Excel workbook
8. Select Get Data > From Other Sources > Web
9. Paste the link into the URL box and click OK
10. Select ‘Organisation Account’ and check the root path is correct
11. If requested, log in with your Office 365 credentials
12. This will bring up the navigator dialog where you can choose how to load or transform the connected data into Power Query from the Excel file in SharePoint
I hope this helps
Kind regards
Martin Sutherland
(IT Trainer)
RE: Excel Power Query Sharepoint Connection
Hi Holly,
To link Power Query to a SharePoint folder and merge multiple Excel files, follow these steps:
1. Navigate to your SharePoint site and locate the folder containing the Excel files. Copy the URL of the folder from your browser's address bar.
2. Open Excel and go to the "Data" tab. Select "Get Data" > "From File" > "From SharePoint Folder." Paste the SharePoint folder URL into the dialog box and click "OK." Authenticate with your Microsoft account if prompted.
3. Power Query will display a list of all files in the folder and its subfolders. Filter the list to include only the Excel files you want to merge (e.g., by file extension or name).
4. Click "Combine & Transform Data" to open the Power Query Editor which will prompt you to select a sample file to define the structure of the data. Make any necessary transformations to the sample file (e.g., renaming columns, filtering rows).
5. Power Query will automatically apply the transformations from the sample file to all files in the folder. You can further refine the combined data in the Power Query Editor.
6. Once you're satisfied with the combined data, click "Close & Load" to load it into Excel or Power BI.
Hope this has helped
Kind regards
Martin
RE: Excel Power Query Sharepoint Connection
This is super helpful. What would be the process of linking to a sharepoint folder location as opposed to a specific doc. i.e my use case might be merging data from all excel files saved in a specific folder...
RE: Excel Power Query Sharepoint Connection
I'm struggling a little with this. I don't suppose there would be the option for a 5 mins call? I expect this is all it would need
RE: Excel Power Query Sharepoint Connection
Hi Holly,
I suspect the issue is down to the root folder path for the Sharepoint site not being correct. In order to access the Excel files within the Sharepoint folder I would suggest you approach your IT dept who will have tenant administration access to this site and explain the issue to them
Unfortunately, our forum service does not include phone calls as this would be deemed as consultancy which is outside the scope of this service
Good luck with finding a solution
Kind regards
Martin
Will be marked as resolved in 5 days
Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.
