https://blog.datumdiscovery.com/blog/read/how-to-auto-refresh-sharepoint-excel-data-in-power-bi-a-step-by-step-guide
How to Auto-Refresh SharePoint Excel Data in Power BI: A Step-by-Step Guide
Power BI

Oct 27, 2024

How to Auto-Refresh SharePoint Excel Data in Power BI: A Step-by-Step Guide

Managing data updates in real-time can be challenging, especially if you’re working with data hosted in SharePoint and visualized in Power BI. Fortunately, automating data refreshes between SharePoint Excel files and Power BI can ensure that your dashboards display the most current information without manual interventions. This guide will walk you through setting up auto-refresh for SharePoint Excel data in Power BI, providing detailed instructions on how to streamline this crucial process.

Table of Contents

  1. Introduction to Power BI and SharePoint Integration
  2. Why Automate Data Refreshes?
  3. Prerequisites for Setting Up Auto-Refresh
  4. Step 1: Uploading Your Excel File to SharePoint Online
  5. Step 2: Connecting Power BI to SharePoint Excel Data
  6. Step 3: Configuring Scheduled Refresh in Power BI
  7. Step 4: Enabling Data Gateway (If Required)
  8. Step 5: Testing and Validating the Auto-Refresh Setup
  9. Troubleshooting Common Issues
  10. Best Practices for Managing Auto-Refresh in Power BI
  11. FAQs

1. Introduction to Power BI and SharePoint Integration

Power BI is a powerful data visualization tool that allows users to connect, transform, and visualize data across various sources. SharePoint, as a part of the Microsoft 365 ecosystem, offers cloud-based storage where you can securely host your Excel data. Integrating Power BI with SharePoint offers a seamless way to harness your SharePoint-hosted data, providing up-to-date insights directly in Power BI dashboards.

2. Why Automate Data Refreshes?

In today’s fast-paced business world, data needs to be both current and accurate. Manually updating data can lead to delays and errors, and it isn’t feasible for organizations handling large datasets. Automating the data refresh between SharePoint and Power BI ensures:

  • Real-time data visibility: Users always see the latest data.
  • Reduced manual effort: Save time with fewer manual data uploads.
  • Enhanced decision-making: Access to the latest data allows for faster, informed decisions.

3. Prerequisites for Setting Up Auto-Refresh

To set up auto-refresh for SharePoint Excel data in Power BI, ensure you have the following:

  • A Power BI Pro or Power BI Premium license, required for scheduled refresh.
  • Microsoft 365 credentials with access to the SharePoint site where the Excel file is stored.
  • The Excel file hosted in SharePoint Online (as opposed to on-premises SharePoint).
  • Admin access in Power BI to configure settings, if not already set up.

4. Step 1: Uploading Your Excel File to SharePoint Online

The first step is to ensure that your Excel file is accessible from SharePoint Online. If your data isn’t yet stored in SharePoint, follow these steps:

  1. Open SharePoint Online and navigate to the document library or site where you’d like to store your Excel file.
  2. Upload the Excel file by clicking on the “Upload” option in the toolbar, then select your file.
  3. Once uploaded, copy the URL of the Excel file. This link will be used when connecting Power BI to the SharePoint file.

Note: Ensure that permissions on the file allow the appropriate users to access it in Power BI.

5. Step 2: Connecting Power BI to SharePoint Excel Data

Now that your Excel file is on SharePoint, connect it to Power BI using the following method:

  1. Open Power BI Desktop and select “Get Data” from the Home tab.
  2. In the “Get Data” window, select SharePoint Folder as the data source, then click “Connect.”
  3. In the next screen, enter the URL of your SharePoint site (not the full Excel file path, just the main SharePoint site URL). This step will enable Power BI to identify files in the SharePoint location.
  4. Authenticate using your Microsoft 365 credentials if prompted.
  5. Once connected, Power BI will display a list of all files in the SharePoint folder. Find your Excel file in the list and select it.
  6. Load the data by choosing the appropriate sheets or tables from your Excel file.

6. Step 3: Configuring Scheduled Refresh in Power BI

With your data loaded, you’re ready to set up an automatic refresh schedule in Power BI Service. Scheduled refreshes ensure Power BI retrieves the latest data from SharePoint regularly.

  1. Publish your Power BI report to the Power BI Service by selecting “Publish” from Power BI Desktop’s toolbar.
  2. Navigate to Power BI Service and open the dataset linked to your published report.
  3. Click on Settings for the dataset, then expand the Scheduled Refresh option.
  4. Toggle on Keep your data up-to-date and configure the refresh schedule. Power BI Pro users can refresh up to eight times daily, while Power BI Premium users have more frequent options.
  5. Set the frequency and times you want the dataset to refresh. Choose options that align with your business’s data update needs.

Note: If you’re using data from multiple sources, Power BI may require a Data Gateway for automatic refresh.

7. Step 4: Enabling Data Gateway (If Required)

If your data resides across different platforms (e.g., both cloud-based and on-premises sources), you may need a Power BI Data Gateway to enable refreshes. Follow these steps if the gateway is required:

  1. Download and install the Power BI Data Gateway from Power BI’s official site.
  2. Configure the gateway with appropriate credentials to access on-premises data.
  3. In Power BI Service, go to Manage Gateways, and add the gateway you installed.
  4. Assign the gateway to your dataset by navigating to dataset settings, and ensure the refresh is enabled.

With the gateway active, Power BI can connect to and refresh datasets from both cloud and on-premises sources.

8. Step 5: Testing and Validating the Auto-Refresh Setup

After configuring scheduled refresh, verify that the data refreshes as expected.

  1. Modify the data in your SharePoint-hosted Excel file by adding a new row or column.
  2. Wait for the next scheduled refresh or manually trigger a refresh in Power BI Service by selecting “Refresh now.”
  3. Check your report or dashboard to confirm that the updated data appears correctly.

9. Troubleshooting Common Issues

If you encounter any issues with the auto-refresh setup, try these troubleshooting tips:

  • Authentication errors: Re-authenticate in Power BI Service and ensure you have the correct permissions on the SharePoint site.
  • Data Gateway issues: If using a Data Gateway, check if it’s running correctly. You can also reinstall or update the gateway if needed.
  • Excel file lock: SharePoint occasionally locks files that are open in Excel; ensure no users are actively editing the file during refresh times.

10. Best Practices for Managing Auto-Refresh in Power BI

To ensure seamless and efficient data refreshes, consider these best practices:

  • Optimize the Excel file size: Large files can slow down refresh times; consider reducing data volume if possible.
  • Minimize unnecessary refreshes: Set refresh times based on actual data update needs to avoid overloading Power BI.
  • Monitor refresh history: Power BI Service offers a refresh history log for each dataset, allowing you to check for errors or delays.
  • Notify users of updates: Set up alerts or notifications in Power BI if data refreshes fail, ensuring stakeholders know when data isn’t current.

11. FAQs

Q1: How frequently can I refresh SharePoint Excel data in Power BI?

Power BI Pro users can schedule up to eight refreshes per day, while Power BI Premium users have options for up to 48 refreshes daily.

Q2: Do I need a Data Gateway for SharePoint Online Excel files?

No, if your Excel file is stored in SharePoint Online. A Data Gateway is only required when integrating with on-premises data sources.

Q3: Can I use this method with SharePoint on-premises?

This guide applies to SharePoint Online. For SharePoint on-premises, you will need to set up a Data Gateway.

Q4: Why isn’t my Power BI data refreshing?

Check your authentication settings, Data Gateway status (if applicable), and verify that the SharePoint Excel file isn’t locked by other users.

Q5: How can I manually trigger a refresh in Power BI Service?

In Power BI Service, go to the dataset, click on Settings, and select Refresh now to manually update the data.

Q6: Can I automate refreshes without Power BI Pro or Premium?

Scheduled refresh in Power BI requires a Pro or Premium license. Free users can only manually refresh data.

Setting up an auto-refresh for SharePoint Excel data in Power BI simplifies data management and ensures stakeholders have access to the most accurate and up-to-date insights. By following these steps and implementing best practices, you can create a reliable, automated data pipeline that enhances decision-making across your organization.


For more detailed guidance and in-depth training, visit our training here.

Tags: Power BI SharePoint

Author: Nirmal Pant