Automating Excel Data Refresh from SharePoint to Power BI

Introduction

Keeping your Power BI reports synced with Excel data stored on SharePoint can be tricky. Fortunately, Power BI offers a seamless way to automate data refreshes, ensuring your reports are always up to date without any manual effort.

In this guide, we’ll walk you through setting up automatic refreshes for Excel files on SharePoint in Power BI. With these steps, you can keep your reports current and provide stakeholders with the latest insights—all without lifting a finger.

Connecting to the Excel File on SharePoint

Step 1: Get the SharePoint File Path Start by locating your Excel file on SharePoint:

1. Navigate to the SharePoint site where your file is stored.
2. Find your Excel file (e.g., AirportsData.csv).
3. Click the information icon (ℹ️) to open the details pane.

Scroll to the "Path" section and copy the file URL.

Step 2: Connect to the File in Power BI Desktop Now, open Power BI Desktop:

1. Go to the Home tab and click Get Data.
2. Select Web as the data source.
3. Paste the SharePoint file URL in the URL field and click OK.

Step 3: Authenticate Your Connection Power BI will ask you to authenticate:

1. Choose Organizational account as the authentication method.
2. Click on Sign in.
3. Enter your organizational account credentials associated with SharePoint.

Once signed in, click Connect.

Step 4: Load the Data Once the connection is established:

1. Power BI will show you a preview of the data.
2. If everything looks good, click Load to import the data into Power BI.
3. If you need to make adjustments, click Transform Data to open the Power Query Editor for any necessary changes.

Publishing the Report to Power BI Service

Once your report is ready in Power BI Desktop, the next step is to publish it to the Power BI Service.

 Step 1: Sign In to Power BI Service

To begin:

● In Power BI Desktop, click on Sign In (if you haven’t already).

● Enter your Power BI Pro account credentials.

Step 2: Publish the Report

Then:

● Click on Publish in the Home tab.

● Choose the desired workspace where you want to publish the report.

● Finally, click Select to confirm.

Tip: Even though your Power BI Desktop file is saved locally, the data source (Excel file) resides on SharePoint.

Configuring Automatic Data Refresh

To ensure your report automatically refreshes when the Excel file changes, follow these steps:

Step 1: Access the Dataset Settings

In Power BI Service:

● Navigate to the workspace where you published the report.

● Find your dataset, click on the ellipsis (⋯) next to it, and select Settings from the dropdown menu.

Step 2: Edit Data Source Credentials

Within the Settings page:

● Expand the Data source credentials section.

● You may see a warning about missing credentials. Click on Edit credentials.

● Select OAuth2 as the authentication method.

● Set the Privacy Level to Organizational.

● Then, click Sign in and authenticate with your organizational account.

Step 3: Verify the Connection

After updating the credentials:

● Ensure that the connection status shows as Connected without any errors.

● If there are issues, double-check your credentials and privacy level settings.

 Understanding the Refresh Process

According to Microsoft’s documentation on refreshing datasets imported from Excel workbooks on OneDrive or SharePoint Online:

● Automatic Checks: Power BI automatically checks for updates to your Excel file approximately every hour.

● Dataset Refresh: If it detects any changes, it refreshes the dataset and updates the associated reports and dashboards.

Key Points:

● No Data Gateway Required: Since the data source is in the cloud (SharePoint Online), you don’t need to set up an on-premises data gateway.

● Automatic Refresh: Power BI handles the refresh process without manual intervention.

● Refresh Frequency: The automatic refresh occurs roughly every hour, but this is managed by the Power BI Service and cannot be adjusted for this type of connection.

Tips and Best Practices

To make the most of this setup, consider the following tips:

● Use Organizational Accounts: Always authenticate using your organizational account to ensure secure access.

● Monitor Data Changes: When you update the Excel file on SharePoint, allow up to an hour for the changes to reflect in Power BI.

● Perform On-Demand Refreshes: If you need immediate updates, you can manually trigger a refresh in Power BI Service:

○ Navigate to your dataset.

○  Click on the Refresh Now option.

Note: There are limits on the number of on-demand refreshes you can perform per day, depending on your Power BI license.

● Check Refresh History: Regularly review the Refresh History in the dataset settings to monitor the refresh process and troubleshoot any issues.

● Optimize Your Data Model: Keeping your data model efficient reduces refresh times and improves performance.

Conclusion

By following these steps, you can effortlessly set up an automatic data refresh from Excel files stored on SharePoint in Power BI. This ensures that your reports are always up-to-date, allowing you and your stakeholders to make informed decisions based on the latest data. Automating the refresh process not only saves time but also enhances the reliability of your reporting solutions.