Microsoft Lists is a great way to share data; other users can view and even edit these lists if the author gives permission. And for our audience, it’s important to know that there’s a special connector in Microsoft Power BI for Microsoft Lists.
But for interested readers, my first suggestion is this: If you’re deciding 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 can’t use SharePoint Lists, you can still have Power BI connect to your Microsoft Lists files. The solution just isn’t super obvious.
SEE: Hiring Pack: Microsoft Power BI Developer (Tech Republic Premium)
In this tutorial, I’ll show you how to connect Power BI Desktop to Microsoft Lists using the SharePoint Online List connector. It’s not a difficult process, but then again, it might not be the first place you look when you’re figuring out how to connect to Microsoft Lists.
To demonstrate this connection, I will use Microsoft Power BI Desktop and Microsoft lists Microsoft 365 on a Windows 10 64-bit system. If you’re not familiar with using Microsoft Lists, start reading Use Microsoft 365 lists to organize Excel data that you need to track or share.
Here you can Microsoft Power BI demo file for this tutorial and follow along.
Jump to:
Microsoft Lists vs. Microsoft SharePoint Lists
Technically, Microsoft Lists and SharePoint Lists are the same; Microsoft Lists is simply an interface to SharePoint. However, you may be using Microsoft Lists and never realize you are working with SharePoint. That means you may not realize that you can use a SharePoint connector to connect Power BI and Microsoft Lists.
How to integrate Microsoft Lists files with SharePoint
If the Microsoft Lists file you’re using is stored on a SharePoint site, you’re in luck because you can generate a Power BI report based on that list in a few quick clicks. To see how easy this process is, open the Microsoft Lists file as you normally would and follow these steps:
1. Open the list and click Integrate in the menu. If this option is not available, deselect all selected records to hide the context menu.
2. Choose Power BI from the resulting drop-down list.
3. In the Visualize submenu, choose the list (Image A).
Image A
4. When the import is complete, click the resulting report link in the top right corner.
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 working with new data.
Figure B
As mentioned, this fast reporting option is possible thanks to SharePoint. But as good as this reporting process is, the report isn’t dynamic – you just created a new .pbix file. To use list data in an existing file, you must import that data.
Import data from Microsoft Lists into Power BI
The integration feature is powerful and effective for quickly prototyping or giving you, the designer, a quick start. To use Microsoft Lists data in an existing Power BI file, you must import the data using a SharePoint connector.
SEE: The complete Microsoft Power BI Super Bundle (TechRepublic Academy)
If you own both the list and the .pbix file, you should be able to connect without any problems. If you don’t own the list, ask the owner to grant you permission to access the file before doing anything else.
Copy the SharePoint address
Now suppose you have a Microsoft Lists file that you want to use in Power BI. Sometimes the possibilities can be overwhelming and we completely miss what is 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 need to grab the Microsoft List file URL. To do this, open the list in Microsoft Lists and copy the URL address.
Now this part gets a bit muddy because you don’t need the whole address — you only want to use the address up to the /Lists component. You can selectively copy it from the URL address checker, but this is a bit tricky. You can also copy the entire URL to a text document and then copy only the part of the URL you need.
Figure C shows a list address. Note that the /Lists component and everything after it is not included. You only need the first part: the SharePoint address.
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 follow these steps:
1. Choose More from the Get Data drop-down list.
2. Choose Online Services in the left pane of the resulting dialog box.
3. On 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 Deployment.
7. Expand the Advanced options section.
8. In the Display Mode drop-down list, choose the default option (Figures E). If you choose the All option, you import a lot of metadata that you don’t need.
Figures E
9. Click Connect.
10. In the resulting dialog box, find the Microsoft Lists file and check it in the list on the left (Figure F).
Figure F
11. Click Load. You can click Transform Data if needed when working with your own files. Doing so allows you to set up data types before loading them into Power BI.
Figure G shows the new table in the Fields pane and a table visualization that lets you compare the data to the actual list.
Figure G
Latest tips and tricks for using Power BI and Microsoft Lists together
If you run into problems at any point in this process, check the permissions on 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.
SEE: Short Glossary: Business intelligence and analytics (Tech Republic Premium)
Remember, all you need is your SharePoint address. If you copy the /Lists component and/or the name of the open list, Power BI returns an error. Getting data into Microsoft Power BI is easy because there are so many special connectors and even custom connectors.
Read more: Best business intelligence tools (TechRepublic)
