Forum Discussion

Post Prodigy

1 year ago

Solved

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....

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

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.