How to Use Microsoft Lists With Power BI

To use Microsoft Lists data in Power BI, you can use a SharePoint connector for quick access. I’ll show you how.

Microsoft Lists is a great way to share data; other users can view and even edit these lists if the author gives permission. If you have a Microsoft 365 license, you already have it, and it’s easy to use. In addition, it’s easy to import Microsoft Lists data into Power BI for further analysis.

There’s no reason to go looking for another, perhaps more difficult, solution when Microsoft Lists is available. In fact, if you’re willing to load the list data into a new Power BI file, you don’t even have to import the data — you can integrate the data with Power BI from inside Microsoft Lists.

In this tutorial, I’ll show you how to connect Power BI Desktop to Microsoft Lists by using the SharePoint Online List connector. It isn’t a difficult process, but it might not be the first place you look when figuring out how to connect to Microsoft Lists.

Jump to:

Follow along

To demonstrate this connection, I’ll be using Microsoft Power BI Desktop and Microsoft Lists with Microsoft 365 on a Windows 11 64-bit system. If you’re not familiar with how to use Microsoft Lists, you might want to start by reading How to use Microsoft 365 Lists to organize Excel data you must track or share.

SEE: Here’s how to sync Microsoft Lists with Microsoft Excel.

You can download the Microsoft Excel data for this tutorial and follow along.

Microsoft Lists vs. Microsoft SharePoint Lists

Technically, Microsoft Lists and SharePoint Lists are the same thing; Microsoft Lists is simply an interface to SharePoint. However, it’s possible for you to use Microsoft Lists and never realize you’re working with SharePoint. That means you might not realize you can use a SharePoint connector to connect Power BI and Microsoft Lists.

But for interested readers, my first suggestion is this: If you’re deciding on whether to use Microsoft Lists or not and Power BI is a potential destination for that data, don’t use Microsoft Lists. If possible, use SharePoint Lists instead. However, if you’re in a position where you cannot use SharePoint Lists, you can still get Power BI to connect to Microsoft Lists files. The solution just isn’t super obvious.

How to integrate Microsoft Lists files with SharePoint

If the Microsoft Lists file you’re using is saved to a SharePoint site, you’re in luck because you can generate a Power BI report based on that list with a few quick clicks. To learn how simple this process is, open the Microsoft Lists file as you normally would, and then:

1. With the list open, click Integrate on the menu. If this option isn’t available, unselect all selected records to hide the contextual menu.
2. Choose Power BI from the resulting dropdown.
3. From the submenu, choose Visualize the List (Figure A).

Figure A

Power BI imports the list data into Power BI (Figure B). The Microsoft Power BI platform builds visuals based on the list data and publishes the report. This is a great starting point for a designer who is working with new data.

Figure B

As mentioned, this quick reporting capability is possible thanks to SharePoint. But as good as this reporting process is, the report isn’t dynamic — you’ve simply created a new .pbix file. If you want to use list data in an existing file, you’ll need to import that data.

How to import Microsoft Lists data into Power BI

The integration feature is powerful and effective at creating a quick prototype or giving you, the designer, a quick start. If you want to use Microsoft Lists data in an existing Power BI file, you’ll have to import the data using a SharePoint connector.

SEE: Learn all you can about Power BI with this bundle from TechRepublic Academy.

If you own both the list and the .pbix file, you should be able to connect without difficulty. If you don’t own the list, ask the owner to grant you permission to access the file before you do anything else.

Copy the SharePoint address

Now, let’s suppose you have a Microsoft Lists file you want to use in Power BI. Sometimes the possibilities can be overwhelming, and we totally miss what’s available. This route I’m going to show you is one of those unexpected solutions unless you know what to look for.

Before you can do anything else, you’ll need to grab the URL of the Microsoft List file. To do so, simply open the list in Microsoft Lists and copy the URL address.

Now, this part gets a bit muddy because you don’t need the entire address — you want to use the address only up to the /Lists component. You can selectively copy it from the URL address control, but doing so is a bit awkward. Alternatively, you can copy the entire URL into a text document and then use only the part of the URL you need from there.

Figure C shows a list address. Notice the component /Lists and everything following them isn’t included. You need only the first part: the SharePoint address. You’re only copying the location where the list is stored.

Figure C

Import the list data

Now, let’s import the list data into an existing Power BI file. First, open Power BI Desktop as you normally would and then:

1. From the Get Data dropdown, choose More.
2. From the left pane in the resulting dialog, choose Online Services.
3. To the right, select SharePoint Online List (Figure D).

Figure D

4. Click Connect.
5. When prompted, paste the URL address with your SharePoint address into the Site URL setting.
6. Click 2.0 under Implementation.
7. Expand the Advanced Options section.
8. From the View Mode dropdown, choose the default option (Figure E). If you choose the All option, you’ll import a lot of metadata you don’t need.

Figure E

9. Click Connect. If prompted, choose a sign-in account, most likely your Microsoft account, and click Connect.
10. In the resulting dialog box, find the Microsoft Lists file, and check it in the list to the left (Figure F).

Figure F

11. Click Load. You can click Transform Data, if necessary, when working with your own files. Doing so lets you set data types before loading into Power BI, but that’s not necessary for this example. This process might take a minute or so.

Figure G shows the new table in the Fields pane and a Table visualization, which allows you to check the data against the actual list.

Figure G

Final tips and tricks for using Power BI and Microsoft Lists together

If you have difficulty at any point in this process, review permissions for the Microsoft Lists file you’re pulling into Power BI. If that’s not the problem, check the URL address you entered in step five of Copy the SharePoint address.

PREMIUM: Check out this quick glossary for business intelligence and analytics terms.

Remember, you need only your SharePoint address. If you copy the /Lists component and/or the name of the open list, Power BI will return an error. Getting data into Microsoft Power BI is easy because there are so many dedicated connectors and even custom connectors.