How to Use Microsoft Lists With Power BI
Your email has been sent
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:
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.
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.
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:
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.
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.
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
Now, let’s import the list data into an existing Power BI file. First, open Power BI Desktop as you normally would and then:
Figure D
Figure E
Figure F
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
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.
From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays
From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays
How to Use Microsoft Lists With Power BI
Your email has been sent
TechRepublic Premium content helps you solve your toughest IT issues and jump-start your career or next project.
This is a comprehensive list of the best AI art generators. Explore the advanced technology that transforms imagination into stunning artworks.
Find the perfect payroll service for your business without breaking the bank. Discover the top cheap payroll services, features, pricing and pros and cons.
Is NordVPN worth it? How much does it cost and is it safe to use? Read our NordVPN review to learn about pricing, features, security, and more.
Free project management software provides flexibility for managing projects without paying a cent. Check out our list of the top free project management tools.
Australian and New Zealand enterprises in the public cloud are facing pressure to optimize cloud strategies due to a growth in usage and expected future demand, including for artificial intelligence use cases.
Zero-trust security operates on the fundamental premise that trust should never be assumed, regardless of whether a user or device is inside or outside the corporate network. In this TechRepublic Premium guide, we will explore the benefits of incorporating zero-trust security into your organization’s cybersecurity framework to prevent data breaches and strengthen the protection of …
Every organization relies on data that must be protected and backed up in a reliable and secure way by authorized personnel. While the content or criticality of the data may vary, the processes behind a successful backup strategy are universal. This checklist from TechRepublic Premium will help you establish a standard framework for backing up …
Artificial intelligence comes in many varieties, from tools that respond to customers via chat to complicated machine learning algorithms that predict the trajectory of an entire organization. Despite years of activity and the imaginative ideas found in science fiction, AI doesn’t yet comprise sentient machines that reason like humans. Rather, AI encompasses more narrowly focused …
Data governance is no longer a ‘nice-to-have’ measure in organizations as there is a growing concern around the globe that unregulated use of data can result in serious privacy violations, among other issues. That’s why data governance has topped the agenda in many continents as more efforts are being put in place to provide standards …
Get the web’s best business technology news, tutorials, reviews, trends, and analysis—in your inbox. Let’s start with the basics.
* – indicates required fields
Lost your password? Request a new password
Please enter your email adress. You will receive an email message with instructions on how to reset your password.
Check your email for a password reset link. If you didn’t receive an email don’t forgot to check your spam folder, otherwise contact support.
This will help us provide you with customized content.
Thanks for signing up! Keep an eye out for a confirmation email from our team. To ensure any newsletters you subscribed to hit your inbox, make sure to add [email protected] to your contacts list.
Leave a Reply