How you can Use Microsoft Lists With Energy BI


To make use of Microsoft Lists knowledge in Energy BI, you should use a SharePoint connector for fast entry. I will present you the way.

Microsoft Lists is a good way to share knowledge; different customers can view and even edit these lists if the creator provides permission. When you’ve got a Microsoft 365 license, you have already got it, and it’s straightforward to make use of. As well as, it’s straightforward to import Microsoft Lists knowledge into Power BI for additional evaluation.

There’s no cause to go on the lookout for one other, maybe harder, answer when Microsoft Lists is offered. In truth, when you’re keen to load the checklist knowledge into a brand new Energy BI file, you don’t even need to import the information — you possibly can combine the information with Energy BI from inside Microsoft Lists.

On this tutorial, I’ll present you how you can join Energy BI Desktop to Microsoft Lists by utilizing the SharePoint On-line Listing connector. It isn’t a troublesome course of, but it surely may not be the primary place you look when determining how to connect with Microsoft Lists.

Bounce to:

Observe alongside

To reveal this connection, I’ll be utilizing Microsoft Power BI Desktop and Microsoft Lists with Microsoft 365 on a Home windows 11 64-bit system. In the event you’re not accustomed to how you can use Microsoft Lists, you may need to begin by studying How to use Microsoft 365 Lists to organize Excel data you must track or share.

SEE: Right here’s how you can sync Microsoft Lists with Microsoft Excel.

You may obtain the Microsoft Excel data for this tutorial and observe alongside.

Microsoft Lists vs. Microsoft SharePoint Lists

Technically, Microsoft Lists and SharePoint Lists are the identical factor; Microsoft Lists is just an interface to SharePoint. Nonetheless, it’s attainable so that you can use Microsoft Lists and by no means notice you’re working with SharePoint. Which means you may not notice you should use a SharePoint connector to attach Energy BI and Microsoft Lists.

However for readers, my first suggestion is that this: In the event you’re deciding on whether or not to make use of Microsoft Lists or not and Energy BI is a possible vacation spot for that knowledge, don’t use Microsoft Lists. If attainable, use SharePoint Lists as an alternative. Nonetheless, when you’re able the place you can not use SharePoint Lists, you possibly can nonetheless get Energy BI to connect with Microsoft Lists recordsdata. The answer simply isn’t tremendous apparent.

How you can combine Microsoft Lists recordsdata with SharePoint

If the Microsoft Lists file you’re utilizing is saved to a SharePoint web site, you’re in luck as a result of you possibly can generate a Energy BI report based mostly on that checklist with just a few fast clicks. To learn the way easy this course of is, open the Microsoft Lists file as you usually would, after which:

  1. With the checklist open, click on Combine on the menu. If this selection isn’t obtainable, unselect all chosen information to cover the contextual menu.
  2. Select Energy BI from the ensuing dropdown.
  3. From the submenu, select Visualize the Listing (Determine A).

Determine A

Use the Integrate options to quickly retrieve the Microsoft Lists file for Power BI.
Use the Combine choices to rapidly retrieve the Microsoft Lists file for Energy BI.

Energy BI imports the checklist knowledge into Energy BI (Determine B). The Microsoft Energy BI platform builds visuals based mostly on the checklist knowledge and publishes the report. It is a nice place to begin for a designer who’s working with new knowledge.

Determine B

Power BI quickly creates a report based on data in the Microsoft Lists file.
Energy BI rapidly creates a report based mostly on knowledge within the Microsoft Lists file.

As talked about, this fast reporting functionality is feasible due to SharePoint. However pretty much as good as this reporting course of is, the report isn’t dynamic — you’ve merely created a brand new .pbix file. If you wish to use checklist knowledge in an present file, you’ll must import that knowledge.

How you can import Microsoft Lists knowledge into Energy BI

The mixing function is highly effective and efficient at creating a fast prototype or supplying you with, the designer, a fast begin. If you wish to use Microsoft Lists knowledge in an present Energy BI file, you’ll need to import the information utilizing a SharePoint connector.

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

In the event you personal each the checklist and the .pbix file, you need to be capable of join with out problem. In the event you don’t personal the checklist, ask the proprietor to grant you permission to entry the file earlier than you do the rest.

Copy the SharePoint deal with

Now, let’s suppose you may have a Microsoft Lists file you need to use in Energy BI. Generally the probabilities might be overwhelming, and we completely miss what’s obtainable. This route I’m going to point out you is a kind of surprising options until you realize what to search for.

Earlier than you are able to do the rest, you’ll must seize the URL of the Microsoft Listing file. To take action, merely open the checklist in Microsoft Lists and duplicate the URL deal with.

Now, this half will get a bit muddy since you don’t want your complete deal with — you need to use the deal with solely as much as the /Lists part. You may selectively copy it from the URL deal with management, however doing so is a bit awkward. Alternatively, you possibly can copy your complete URL right into a textual content doc after which use solely the a part of the URL you want from there.

Determine C exhibits an inventory deal with. Discover the part /Lists and every thing following them isn’t included. You want solely the primary half: the SharePoint deal with. You’re solely copying the placement the place the checklist is saved.

Determine C

Copy your SharePoint address into a text document or to the Clipboard.
Copy your SharePoint deal with right into a textual content doc or to the Clipboard.

Import the checklist knowledge

Now, let’s import the checklist knowledge into an present Energy BI file. First, open Energy BI Desktop as you usually would after which:

  1. From the Get Knowledge dropdown, select Extra.
  2. From the left pane within the ensuing dialog, select On-line Companies.
  3. To the suitable, choose SharePoint On-line Listing (Determine D).

Determine D

Choose the SharePoint Online List connector.
Select the SharePoint On-line Listing connector.
  1. Click on Join.
  2. When prompted, paste the URL deal with together with your SharePoint deal with into the Website URL setting.
  3. Click on 2.0 underneath Implementation.
  4. Develop the Superior Choices part.
  5. From the View Mode dropdown, select the default choice (Determine E). In the event you select the All choice, you’ll import numerous metadata you don’t want.

Determine E

You must tell the connector where to find the list file.
You have to inform the connector the place to search out the checklist file.
  1. Click on Join. If prompted, select a sign-in account, most probably your Microsoft account, and click on Join.
  2. Within the ensuing dialog field, discover the Microsoft Lists file, and test it within the checklist to the left (Determine F).

Determine F

Select the Microsoft Lists file you want to use in Power BI.
Choose the Microsoft Lists file you need to use in Energy BI.
  1. Click on Load. You may click on Remodel Knowledge, if crucial, when working with your individual recordsdata. Doing so enables you to set knowledge sorts earlier than loading into Energy BI, however that’s not crucial for this instance. This course of may take a minute or so.

Determine G exhibits the brand new desk within the Fields pane and a Desk visualization, which lets you test the information in opposition to the precise checklist.

Determine G

Using the SharePoint connector, you can import Microsoft Lists data into Power BI.
Utilizing the SharePoint connector, you possibly can import Microsoft Lists knowledge into Energy BI.

Closing suggestions and methods for utilizing Energy BI and Microsoft Lists collectively

When you’ve got problem at any level on this course of, assessment permissions for the Microsoft Lists file you’re pulling into Energy BI. If that’s not the issue, test the URL deal with you entered in step 5 of Copy the SharePoint deal with.

PREMIUM: Take a look at this quick glossary for business intelligence and analytics phrases.

Keep in mind, you want solely your SharePoint deal with. In the event you copy the /Lists part and/or the title of the open checklist, Energy BI will return an error. Getting knowledge into Microsoft Energy BI is simple as a result of there are such a lot of devoted connectors and even customized connectors.



Source link