Making SharePoint link the data source for a report

Hi there,

I have a Power BI report that I have on my personal one drive.

I'm looking to upload the report and Excel to a SharePoint online which I will also want to make the new data source.

How can I do this so that then when I upload the Excel and refresh in the SharePoint then the data would update?

Any help would be appreciated.

Thank you.

Need Help

123abc
1 year ago
To use a SharePoint Online Excel file as the data source for your Power BI report, you can follow these steps:
1. Upload the Excel File to SharePoint Online
Navigate to your SharePoint Online document library.
Upload the Excel file to the desired location in SharePoint.
2. Get the SharePoint File URL
Open the uploaded Excel file in SharePoint Online.
Copy the URL from the browser. It should look like:
https://<tenant>.sharepoint.com/sites/<sitename>/Shared%20Documents/<filename>.xlsx.
3. Modify the URL for Power BI
Replace everything after Shared Documents with the exact file path in the URL, so it ends with .xlsx.
Ensure there are no special characters like %20 (replace it with spaces if necessary).
4. Connect Power BI to SharePoint File
Open your Power BI Desktop report.
Go to Home > Get Data > More.
Select SharePoint Folder or Web as the connector (depending on preference):
Using SharePoint Folder:
Enter the SharePoint root URL (e.g., https://<tenant>.sharepoint.com/sites/<sitename>).
Navigate to the specific Excel file in the list of files.
Using Web:
Paste the modified URL from Step 3.
Authenticate using your Microsoft 365 credentials if prompted.
5. Update Power BI Queries
After connecting to the SharePoint file, ensure your queries point to the new source:
Open Power Query Editor.
Replace references to your OneDrive file with the new SharePoint data source.
Test the connection by clicking Refresh Preview.
6. Publish the Report to Power BI Service
Publish the updated Power BI report to the Power BI Service.
Ensure your Excel file remains in the same SharePoint location.
7. Set Up Scheduled Refresh
In the Power BI Service, navigate to the Settings of the dataset.
Under Data Source Credentials, authenticate the SharePoint data source with your credentials.
Enable Scheduled Refresh and set the refresh frequency as needed.
This setup ensures that any updates to the Excel file in SharePoint will reflect in your Power BI report after a refresh. Let me know if you need detailed guidance on any step!
Anonymous
1 year ago
Hi gbarr12345 ,

Please follow these steps:

1.Change your data source

2.Verify that the account you enter here can log in to the SharePoint site

3.Update Power BI Desktop to the latest version

4.Clear all global permissions

Best Regards,
Wenbin Zhou

14 Replies

123abc
Community Champion
1 year ago

To use a SharePoint Online Excel file as the data source for your Power BI report, you can follow these steps:
1. Upload the Excel File to SharePoint Online
Navigate to your SharePoint Online document library.
Upload the Excel file to the desired location in SharePoint.
2. Get the SharePoint File URL
Open the uploaded Excel file in SharePoint Online.
Copy the URL from the browser. It should look like:
https://<tenant>.sharepoint.com/sites/<sitename>/Shared%20Documents/<filename>.xlsx.
3. Modify the URL for Power BI
Replace everything after Shared Documents with the exact file path in the URL, so it ends with .xlsx.
Ensure there are no special characters like %20 (replace it with spaces if necessary).
4. Connect Power BI to SharePoint File
Open your Power BI Desktop report.
Go to Home > Get Data > More.
Select SharePoint Folder or Web as the connector (depending on preference):
Using SharePoint Folder:
Enter the SharePoint root URL (e.g., https://<tenant>.sharepoint.com/sites/<sitename>).
Navigate to the specific Excel file in the list of files.
Using Web:
Paste the modified URL from Step 3.
Authenticate using your Microsoft 365 credentials if prompted.
5. Update Power BI Queries
After connecting to the SharePoint file, ensure your queries point to the new source:
Open Power Query Editor.
Replace references to your OneDrive file with the new SharePoint data source.
Test the connection by clicking Refresh Preview.
6. Publish the Report to Power BI Service
Publish the updated Power BI report to the Power BI Service.
Ensure your Excel file remains in the same SharePoint location.
7. Set Up Scheduled Refresh
In the Power BI Service, navigate to the Settings of the dataset.
Under Data Source Credentials, authenticate the SharePoint data source with your credentials.
Enable Scheduled Refresh and set the refresh frequency as needed.
This setup ensures that any updates to the Excel file in SharePoint will reflect in your Power BI report after a refresh. Let me know if you need detailed guidance on any step!
- 123abc
  Community Champion
  1 year ago
  I have also attacehd detail steps with screen shots for your help on one_drive. you can reach with this below to those detailed screen short step for easre of your issue.
  If you have any problem please contacht with me on forum.
  
  https://drive.google.com/drive/folders/1dpnVZ3xWncbY3KRT0FcnIlRL8vbTm4el?usp=sharing
  - gbarr12345
    Post Prodigy
    1 year ago
    Hi there,
    
    Thank you for your response.
    
    I followed your steps but when I got to the data source part I'm getting the following error when I put the file link in:
    
    How do I work around this?
- gbarr12345
  Post Prodigy
  1 year ago
  Thank you very much for your help! Really appreciate it!
rajendraongole1
Super User
1 year ago
Hi gbarr12345 - Please find the process flow :

1. Upload the Files to SharePoint Online
Navigate to the desired SharePoint Online library where you want to upload your files (e.g., "Documents" in the Operations Hub site as shown in your image).
Upload the Excel file:
Click on "Upload" and select your Excel file from your local system.
Upload the Power BI report:
If needed, you can upload the Power BI (.pbix) file for storage, but note that the primary interaction with the report will happen in Power BI Service.
2. Update the Data Source in Power BI Desktop
Open the Power BI report in Power BI Desktop:
Open your .pbix file that currently points to the Excel file stored on your OneDrive.
Change the data source to SharePoint Online:
Go to the Home tab and select Transform data > Data source settings.
Locate the connection referencing the current Excel file and select Change Source.
For SharePoint Online:
Use the URL of the SharePoint document library (e.g., https://[YourOrganization].sharepoint.com/sites/[SiteName]/Shared Documents/[FolderName]/[FileName].xlsx).
Ensure you authenticate with your SharePoint Online credentials (you may use either Organizational Account or Web options depending on your setup).
Apply the changes:
Ensure the updated connection is functional by refreshing the data.
3. Publish the Report to Power BI Service
After updating the data source, publish the report to Power BI Service:
Go to the Home tab in Power BI Desktop.
Select Publish and choose the relevant workspace in Power BI Service.
4. Schedule Data Refresh
Configure the Gateway (if required):
If your Excel file resides on SharePoint Online (cloud), no gateway is needed. However, ensure proper permissions.
Set up the Refresh Schedule:
In Power BI Service, navigate to your dataset under the workspace where the report is published.
Configure the refresh schedule using your organizational credentials for SharePoint Online.
5. Automate Updates
To automate updates:
Simply replace the Excel file on SharePoint Online with the updated version, ensuring the structure (e.g., columns and sheet names) remains consistent.
The next data refresh in Power BI Service will pull in the updated data.

Hope this information helps.
- gbarr12345
  Post Prodigy
  1 year ago
  Hi Rajendra,
  
  I followed your steps and when I got to the changing data source part I am getting the following errors:
  
  The first error is the link of the whole SharePoint folder where the Excel is within:
  
  I also tried the link of just the Excel file:
  
  Any idea the right way to do this or the best way to get past this?