Image: PhotoGranary/Adobe Stock Microsoft Lists is a great way to share data; other users can view and even edit these lists if the author allows. And for our audience, it is very important to understand there is a devoted connector in Microsoft Power BI for Microsoft Lists.
However for interested readers, my very first recommendation is this: If you’re choosing whether to use Microsoft Lists or not and Power BI is a potential destination for that information, do not use Microsoft Lists. If possible, utilize SharePoint Lists rather. Nevertheless, if you’re in a position where you can not utilize SharePoint Lists, you can still get Power BI to connect to your Microsoft Lists files. The service just isn’t extremely obvious.
SEE: Hiring kit: Microsoft Power BI developer (TechRepublic Premium)
In this tutorial, I’ll reveal you how to connect Power BI Desktop to Microsoft Lists by using the SharePoint Online List port. It isn’t a difficult procedure, but once again, it might not be the top place you look when figuring out how to link to Microsoft Lists.
To demonstrate this connection, I’ll be utilizing Microsoft Power BI Desktop and Microsoft Lists with Microsoft 365 on a Windows 10 64-bit system. If you’re not knowledgeable about how to use Microsoft Lists, you might wish to begin by checking out How to utilize Microsoft 365 Lists to organize Excel data you need to track or share.
Here, you can download the Microsoft Power BI demonstration file for this tutorial and follow along.
Dive to:
Microsoft Lists vs. Microsoft SharePoint Lists
Best business software
Technically, Microsoft Lists and SharePoint Lists are the exact same thing; Microsoft Lists is simply a user interface to SharePoint. Nevertheless, it’s possible for you to use Microsoft Lists and never realize you’re dealing with SharePoint. That implies you may not understand you can utilize a SharePoint connector to connect Power BI and Microsoft Lists.
How to incorporate Microsoft Lists files with SharePoint
If the Microsoft Lists file you’re using is conserved to a SharePoint site, you’re in luck due to the fact that you can generate a Power BI report based upon that list with a couple of quick clicks. To learn how easy this process is, open the Microsoft Lists file as you generally would, and after that, take the following actions:
1. With the list open, click Incorporate on the menu. If this choice 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
Use the Integrate choices to quickly obtain the Microsoft Lists file for
Power BI. 4. When the import is complete, click the resulting report link in the top-right corner. Power BI imports the list information into Power BI (Figure B ). The Microsoft Power BI platform builds visuals based upon the list data and publishes the report. This is a terrific starting point for a designer who is working with brand-new data.
Figure B
Power BI rapidly develops a report based upon information in the Microsoft Lists file. As pointed out, this fast reporting ability is possible thanks to SharePoint. However as good as this reporting process is, the report isn’t vibrant– you’ve simply produced a new.pbix file. If you wish to use list information in an existing file, you’ll require to import that information.
How to import Microsoft Lists data into Power BI
The combination function is powerful and effective at developing a quick prototype or offering you, the designer, a quick start. If you want to use Microsoft Lists information in an existing Power BI file, you’ll have to import the information utilizing a SharePoint connector.
SEE: The Complete Microsoft Power BI Super Package (TechRepublic Academy)
If you own both the list and the.pbix file, you should have the ability to link without problem. If you don’t own the list, ask the owner to approve you authorization to access the file before you do anything else.
Copy the SharePoint address
Now, let’s suppose you have a Microsoft Lists submit you want to utilize in Power BI. Sometimes the possibilities can be frustrating, and we totally miss what’s readily available. This route I’m going to reveal you is among those unforeseen solutions, unless you understand what to look for.
Before you can do anything else, you’ll require to get 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 due to the fact that you don’t need the whole address– you wish to use the address just approximately the/ Lists part. You can selectively copy it from the URL address control, however doing so is a bit uncomfortable. Additionally, you can copy the whole URL into a text file and after that copy just the part of the URL you require from there.
Figure C shows a list address. Notice the component/ Lists and everything following it aren’t included. You need just the very first part: the SharePoint address.
Figure C
Copy your SharePoint address into a text document or to the Clipboard. Import the list data Now let’s import the list data into an existing Power BI file. First, open Power BI Desktop as you generally would and then take the following steps:
1. From the Get Data dropdown, choose More.
2. From the left pane in the resulting dialog, pick Online Providers.
3. To the right, choose SharePoint Online List (Figure D).
Figure D
Select the SharePoint Online List adapter. 4. Click Link. 5. When triggered, paste the URL address with your SharePoint address
into the Website URL setting. 6. Click 2.0 under Execution.
7. Expand the Advanced Options section.
8. From the View Mode dropdown, select the default choice (Figure E). If you choose the All option, you’ll import a great deal of metadata you don’t require.
Figure E
You must tell the adapter where to find the list file. 9. Click Link. 10. In the resulting dialog, discover the Microsoft Lists file, and examine it in the list to the left(Figure F). Figure F Select the Microsoft Lists submit you wish to utilize in Power BI. 11. Click Load. You can click Transform Data, if necessary, when dealing with your own files. Doing so lets you set data types before filling into Power BI. Figure G reveals the new table in the Fields pane and a Table visualization, which enables you to check the
data versus the real list. Figure G Using the SharePoint adapter, you can import Microsoft Lists information into
Power BI. Final tips and techniques for utilizing Power BI and Microsoft Lists together If you have problem at any point in this procedure, review approvals for the Microsoft Lists submit you’re pulling into Power BI. If that’s not the problem, inspect the URL address you went into in action five of Copy the SharePoint address.
SEE: Quick glossary: Organization intelligence and analytics (TechRepublic Premium)
Remember, you require only your SharePoint address. If you copy the/ Lists part and/or the name of the open list, Power BI will return a mistake. Getting data into Microsoft Power BI is easy due to the fact that there are numerous dedicated connectors and even customized ports.
Check out next: Finest company intelligence tools (TechRepublic)
