How to Connect Power BI to SharePoint Lists

Power BI can connect to SharePoint Lists and visual data that’s coming in from a single user or multiple users that have access to a SharePoint Site. Microsoft has made a number of enhancements in recent years to facilitate easier connections between services.

Connect Power BI to a SharePoint List using Power BI Desktop or create a report directly from the SharePoint List itself

SharePoint lists easily import into Power BI Desktop, and Microsoft even has added capabilities recently to build Power BI reports directly from a SharePoint list. We’ll explain both methods and the pros and cons of using each one.

Let’s jump in!

Why You Should Consider Using SharePoint Lists with Power BI

There are a number of advantages to using SharePoint Lists as a data source for Power BI. The first is that its cloud based. and significantly more robust than using an Excel workbook saved on a network drive with the help of a gateway. And even though you could save an Excel workbook on OneDrive, which gets it into the cloud you wouldn’t have the data governance and control capabilities that you get when working with a SharePoint list.

When a SQL database isn’t available and you need a low code database style option, we really like deploying SharePoint Lists. The Power Platform makes it easy to visual SharePoint list data and recent feature additions to Power Apps make it relatively simple to deploy reactive business apps based on the same list providing for an end to end solution.

Ways to Connect Power BI to SharePoint Data

There are two ways to connect Power BI to a SharePoint List. You can either use Power BI Desktop and establish a connection, or you can use the automatic report builder functionality starting from the SharePoint list. Both work decently well but there are some limitations with the latter.

We’ll explain both methods so you’re aware of what’s possible.

How to Connect Power BI Desktop to a SharePoint List

To connect Power BI to a SharePoint List, launch Power BI Desktop, go to Get Data, More Options, Online Services, and select SharePoint Online List. Authenticate with your Microsoft Login, then enter the root URL of the SharePoint Site. Power BI will give you a list of SharePoint Lists available to import.

Here’s how in more detail.

Step 1.) Launch Power BI Desktop and Get Data

Launch Power BI Desktop, and go to Get Data from the Home tab of the Power BI Ribbon. At the bottom of the list select More… then either search for SharePoint or go to Online Services, SharePoint Online List.

Power BI Get Data options available

Then choose SharePoint Online List

Power BI SharePoint List Get Data Option from SharePoint Online List

Step 2.) Login with your Microsoft Account and Enter the Site URL

After logging in with your Microsoft Account, you’ll be prompted to enter the URL of the SharePoint site that the SharePoint List is hosted on. There’s no need to give the exact URL of the list as Power BI will find all lists available on the site and give you the option to choose the one you need.

In most cases, select 2.0 for the implementation method. Microsoft made some updates to the connector in 2022 adding more features but left the 1.0 version in place for people already using it.

SharePoint Site URL dialog box in Power BI to select the default view or full list from a SharePoint list

The View Mode option at the bottom of the dialog box lets you choose to import all columns from the list or import only visible columns from the default view. SharePoint lists usually have some columns that are hidden from view that are used behind the scenes.

If you default to importing all columns, you can always hide them later in your data model.

Step 3.) Select a SharePoint List to Import

Once you configure the connection and click OK, you’ll be presented with available SharePoint Lists. Select the one or ones that you want and click okay. The data will be imported into Power BI where you can begin visualizing it or modify it further in Power Query.

Selecting a SharePoint List to import into Power BI

SharePoint Lists and Power BI Connection Types

Power BI supports two primary ways to connect to data. DirectQuery which is most common when working with SQL databases and connecting to live data and import mode which gives users more flexibility to model data but has some limitations.

  1. DirectQuery: This type of connection is not applicable for SharePoint lists. DirectQuery is used for live connections to databases, where data is queried directly at the source without importing or copying the data into Power BI. SharePoint lists do not support DirectQuery.
  2. Import Mode: When you connect Power BI to a SharePoint list using the import mode, Power BI imports a copy of the data into the Power BI service. The data is not updated in real-time; instead, it’s updated according to the refresh schedule you set up.

If you’re familiar with working with Excel files and Power BI you’ll notice that the same limitation applies to being an import only data source.

SharePoint Lists and Power BI Refresh Schedules

Because Power BI can only use a SharePoint list as an import dataset it means that data will only refresh during a scheduled refresh. For Pro users, data can be refreshed up to 8 times a day, while Premium users can schedule up to 48 refreshes per day.

To set or change the refresh schedule, you would typically go to the dataset settings in the Power BI service and configure the desired frequency and time for the data refreshes after the report has been published. This allows you to automate the data update process, ensuring your reports and dashboards reflect the latest information from your SharePoint list.

Setting Up Dynamic Refresh Schedules with Power BI

One workaround to scheduling Power BI refreshes at set times of day is to use Power Automate to dynamically refresh datasets based on triggers. For example, you can tell Power Automate to refresh a Power BI dataset whenever a new item is added to a SharePoint list.

You are still limited to the maximum number of refreshes allowed with your Power BI license, but for items that are infrequently updated it’s a nice solution. You can learn more about setting it up with our guide, Dynamically Refresh Power BI Datasets with Power Automate.

Creating a Power BI Report Directly from SharePoint

Another option for creating Power BI reports from SharePoint lists is using the Integrate dropdown from the SharePoint List interface. These are premade links that Microsoft provides to give people a head start on making an Power App, Power BI Report, or Power Automate Flow.

In the case of Power BI, click on Integrate from the SharePoint List view.

The SharePoint List Integrate Power BI dropdown menu

You’ll be prompted to Create a Dashboard using Power BI Online. If you give Power BI a chance it will attempt to use AI to give you a suggested dashboard that’s of questionable value in our opinion, but it’s an interesting feature that will certainly improve over time.

A Power BI report automatically generated by AI using the Power BI integration with SharePoint

Once you complete editing the report, you can Publish to the List, which will place a link under the Integrate, Power BI menu where you can link to the visualized data.

A Power BI report available under the SharePoint Power BI Dropdown Menu after publishing to a list

This is really an interesting feature that gives SharePoint users a fast easy way to generate a report based on underlying SharePoint list data, but it’s somewhat held back because the Power BI Online editor has a limited feature set compared to traditional Power BI desktop at this time.

We also wonder how many people will be aware that a visualized report will be available under the Integrate menu.

Even with limited use cases, we recommend checking back periodically to see how these features continue to evolve as Microsoft improves cross-system integrations.

Conclusion

Building a Power BI report off of a connected SharePoint List has many advantages over using Excel and flat files as a data source. It’s quick and easy to do by getting data in Power BI, selecting the SharePoint List source, logging in, and entering the URL of a SharePoint site that contains the list.

Once you’ve established the connection and built a report, be aware that SharePoint Lists operate on a refresh schedule which may limit some use cases where people are looking for more real-time data as an input source to Power BI.

Microsoft also offers the ability to use the Integrate function to start with a SharePoint list and create a Power BI report either with AI or with the online Power BI Report Builder. It’s a nice feature set but has limited functionality at this point in time but holds a lot of promise.

Scroll to Top