If you've run into the problem of local datasource files being unavailable to others in shared Power BI files or templates, or when you try and publish your report to the Power BI service, use this method to solve it.
By saving the file to SharePoint and connecting to it from Microsoft Power BI you can solve this problem and have a data source that can be updated with changes synced to Power BI based on your Refresh settings for this data source.
Copy the file location from SharePoint
Once you've saved to SharePoint, you need the file location link. But it's not the Shared File link you might normally send someone.
Links like this don't work:
Links that work look like this:
First locate the file in SharePoint Online and click on the Details:
On the right side, you will see the file details. Scroll down until you find the Path.
Click on the Copy Direct Link button next to the Path.
Use the SharePoint file location in Microsoft Power BI
Go to Power BI Desktop and select Get Data.
1. Search for Web and choose the Web data source.
2. Paste your direct link.
You'll be asked to login to your SharePoint using your Organisational Account.
Now you can use the file as a Power BI data source.