Priya has attended:
Excel Advanced course
Power BI Modelling, Visualisation and Publishing course
PowerBI changing source location
Hi,
I have a query where I have created PowerBI reporting connecting to source data which was an Excel file stored on my local drive. These have now been approved and I need to move the file to a shared location on SharePoint which means the source location will change. The idea is that colleagues who have access to this will be able to update the file with new information which should update the PowerBI report. I am having issues with connecting to the new location which is on SharePoint.
Please can you advise on what the best approach to do this is? For example, is there a way that I can replicate the same reports from connected to the local copy but with the new location?
Any help would be appreciated.
Thanks
RE: PowerBI changing source location
Hi Priya,
Thankyou for your question to the forum.
Firstly upload your Excel file to SharePoint. Then do the following:
1. In SharePoint open the Excel file. Then, open the Excel file in the Excel desktop app.
2. 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 or ?d=
6. Select and copy the link
7. Open Power BI Desktop and go to Transform Data to launch Power Query
8. Select your Excel source step (usually called Source)
9. Replace the file path with the SharePoint URL you created above by pasting the path details in the formula bar after = Excel.Workbook(File.Contents(
10. Choose Organizational Account and sign in with your Microsoft 365 credentials
11. Close & Apply - Now your report pulls data from SharePoint directly.
I hope this helps solve the issue
Please let me know if this has worked.
Kind regards
Martin Sutherland
(MOS Trainer)
RE: PowerBI changing source location
Hi Martin,
That was very helpful, thank you. I have now been able to connect PowerBI desktop to the SharePoint location.
I wanted now to be able to recreate the same reports/visuals I had in the local copy to apply in this SharePoint version.
Is there an easy way to do that?
Kind regards,
Priya
RE: PowerBI changing source location
Hi Priya,
I assume your follow up question is about recreating the Power BI report in Sharepoint. If so, here are the steps:
- Publish the desktop report to the service
- Select: File → Embed report → SharePoint Online
- Copy the URL it gives you
(It looks different from the normal report URL — this is important.)
Now go to your SharePoint site:
- Edit the SharePoint page
- Add a Power BI web part
- Paste the embed link you copied
- Publish the SharePoint page
Your report will now appear directly inside SharePoint.
Hope this helps
Kind regards
Martin
