Making SharePoint link the data source for a report

[gbarr12345]

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.

[123abc · Accepted solution]

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

1. Open your Power BI Desktop report.
2. Go to Home > Get Data > More.
3. 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.
4. 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:
  1. Open Power Query Editor.
  2. Replace references to your OneDrive file with the new SharePoint data source.
  3. 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

1. In the Power BI Service, navigate to the Settings of the dataset.
2. Under Data Source Credentials, authenticate the SharePoint data source with your credentials.
3. 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 · Accepted solution]

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

1. Open your Power BI Desktop report.
2. Go to Home > Get Data > More.
3. 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.
4. 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:
  1. Open Power Query Editor.
  2. Replace references to your OneDrive file with the new SharePoint data source.
  3. 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

1. In the Power BI Service, navigate to the Settings of the dataset.
2. Under Data Source Credentials, authenticate the SharePoint data source with your credentials.
3. 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!

[gbarr12345]

Thank you very much for your help! Really appreciate it!

[123abc]

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]

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?

[123abc]

Please follow v-zhouwen-msft direction. its correct.

[v-zhouwen-msft]

Hi all,thanks for the quick reply, I'll add more.

Hi @gbarr12345 ,

Click here to select an account that has access to your sharepoint site to log in

If your problem is solved, please mark 123abc 's answer as the solution

Best Regards,
Wenbin Zhou

[gbarr12345]

Hi Wenbin,

Thanks for your message.

I tried the organisational account option and when I tried logging in with my work email that was the option I'm still getting the error.

Any idea how to get past this?

[v-zhouwen-msft · Accepted solution]

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

[gbarr12345]

Thank you Wenbin. I have that working now so really appreciate it.

Just one more quick question. I have the Power BI report linked to the SharePoint dataset. However, when I update the excel in the SharePoint and I refresh the report it's linked to, the report doesn't update with the new data. 

How do I get the report the dataset is linked to to update with the new data in the App/Service?

App/Service:

Dataset in SharePoint:

Notice the 25/12/2024 isn't showing on the Power BI report.

[v-zhouwen-msft]

Hi @gbarr12345 ,

First of all, I hope you can also mark 123abc 's answer as the solution. I fully agree with his solution.

Regarding your question,

1. update the credentials of your dataset.

2.Manually refresh your dataset or configure a scheduled refresh

3.Refresh your visuals

If you want to modify the excel file in sharepoint, click here

Best Regards,
Wenbin Zhou

[gbarr12345]

I've accepted as a solution now. Thank you very much , I'm sorted now with it. Appreciate the help.

[123abc]

Thank You Very Much v-zhouwen-msft

[rajendraongole1]

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]

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?