Power BI data from Excel on Sharepoint

When teaching others how to work with Power BI, I almost always start with the same example: working with a simple Excel file. Typically, the file is stored on SharePoint, and we import the data into Power BI to explore and work on this basic scenario. This approach isn’t just for demonstration purposes, it also makes sense from a business perspective. Before investing heavily in automating interfaces with data sources like SAP or others, it’s often smarter to start small. Exporting a dataset, saving it in a convenient location (SharePoint being an easy choice for non-local storage), and getting a feel for the data helps prove the concept before scaling up.

That’s why I find it a bit frustrating that retrieving data from a simple Excel file on SharePoint is often more confusing than it should be, especially for beginners. Depending on the method you choose, you might need to deal with different file paths, authentication settings, or connector types, making what should be a simple process feel unnecessarily complex.

In this blog, I’ll walk you through the simplest methods for importing Excel data from SharePoint into Power BI and guide you on how to set up an automatic refresh for your data source.

There are two easy methods to connect to an Excel file stored on SharePoint: syncing the document library to your local device or using the Web Connector. The first method is simpler but doesnt allow you to setup scheduled refreshes.

Method 1: Sync SharePoint site to your local device

The simplest method is to sync your SharePoint site with your local device and use the standard Excel connector. However, as mentioned earlier, this approach doesn’t support scheduled refresh, making it suitable for quick testing but not as a long-term solution. Here’s how you can do it:

Navigate to the SharePoint Document Library

• Open your SharePoint site in a web browser.
• Go to the Documents section (usually found in the left-hand menu).

Sync the Document Library

• Click the Sync button at the top of the document library page.
• Follow the prompts to sync the library with your local device using OneDrive.

Use the Excel Connector in Power BI

• In Power BI Desktop click on Get Data in the Home ribbon.
• Choose Excel as your data source.

Select Your File from the Synced Folder

• Locate the file from the synced folder (you can usually find it in the OneDrive directory, under the name of your SharePoint site)
• Select the file and click Open.

Method 2: Use Web Connector

If you need a scheduled refresh or can’t sync files locally, the best option is to use the Web Connector:

Use the Web Connector

• In the Power Query Editor, click on Home > New Source.
• Select Web from the list of available data sources.

Get the SharePoint File Path

• Navigate to your SharePoint portal and go to the document library where the Excel file is stored.
• Click on the three dots (…) next to the file and choose Details.
• Find the full file path under Path in the details panel.

Load Excel Data

• Copy the SharePoint file path and paste it into the URL field of the Web Connector in Power Query. Click OK.
• In the following Access Web Content window, select Organizational Account for authentication and click Sign in.
• Log in via the browser window that opens, then return to Power BI Desktop and click Connect.
• In the Navigator window, select the desired table and load it into Power BI

Setup automatic refresh

To schedule an automatic refresh you need to deploy the report to the Power BI service and provide the data source credentials.

Edit Data Source Credentials

• Click on the three dots (…) next to the dataset and select Settings.
• Under the Data source credentials section, click Edit credentials.
• Choose OAuth2 as the authentication method:
• Sign in using your organizational account credentials and click Sign in to authorize access.

Set Up a Refresh Schedule

• While still in the Settings page, scroll down to the Scheduled refresh section.
• Enable the Configure a refresh schedule toggle.
• Configure the refresh frequency (e.g., daily) and select the preferred time(s) for refreshes.
• You can add multiple refresh times if needed (maximum of 8 per dataset on pro licence).

By following these steps, you can import Excel data from SharePoint into Power BI and set up scheduled refreshes.

Read More about Power BI:

• Static and dynamic Row-level-Security in Power BI
• Building Gantt Charts with Power BI – Syntera
• Automated Power BI Testing with Selenium
• Power BI Shape Maps: Custom Switzerland Map – Syntera
• Analyse bexio Data with Power BI