How to Connect Microsoft List to Power BI

Transforming a simple list into a dynamic, interactive dashboard can surface insights you never knew were there. By connecting your Microsoft List to Power BI, you can go beyond basic rows and columns to create rich, data-driven reports. This guide will walk you through the entire process, from making the initial connection to building your first visualizations.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Connect Microsoft List to Power BI?

Microsoft List is a fantastic tool for tracking information - tasks, issues, inventory, customer contacts, and more. It’s simple, collaborative, and integrated into the Microsoft 365 ecosystem. But when it comes to analyzing that information, its built-in views can feel limiting.

This is where Power BI comes in. By connecting your list, you unlock the ability to:

• Visualize Data: Turn your list data into engaging charts, graphs, and maps to spot trends and patterns at a glance. How many projects are behind schedule? Which team members are assigned the most tasks? A bar chart can answer this instantly.
• Create Interactive Dashboards: Build reports where clicking on one visual filters all the others. This allows you and your team to explore the data dynamically, drilling down to find the root cause of an issue or identify top performers.
• Combine Data Sources: The true power of Power BI lies in its ability to merge data from multiple places. You can combine your Microsoft List data with financial information from an Excel sheet, sales data from Salesforce, and website traffic from Google Analytics to get a complete picture of business performance.

Before You Begin: What You'll Need

To make this connection, you only need a few things in place. Let’s make sure you’re ready to go before diving into the steps.

• Power BI Desktop: While Power BI has a web service, the initial data connection and modeling are done in the Power BI Desktop application. It's a free download from Microsoft.
• A Microsoft List: You need an existing list with some data in it to connect to.
• SharePoint Site URL: This is the most important - and sometimes trickiest - piece of information you'll need. Every Microsoft List lives on a SharePoint site, even the ones you create under "My lists."

How to Find Your SharePoint Site URL

Don't worry, finding this URL is simple once you know where to look. Navigate to your Microsoft List in your web browser. Now, look at the URL in the address bar. You want to copy the first part of the URL, up to the site name. Everything after /Lists/ can be ignored.

For example, if your list's full URL is: https://yourcompany.sharepoint.com/sites/MarketingTeam/Lists/CampaignTracker/AllItems.aspx

The Site URL you need is just: https://yourcompany.sharepoint.com/sites/MarketingTeam

If you created the list under "My lists," your URL will look something like this, using your name in the path: https://yourcompany-my.sharepoint.com/personal/your_name_yourcompany_com/

Keep this URL handy. You're going to need it in a moment.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step-by-Step: Connecting Microsoft List to Power BI

With Power BI Desktop open and your SharePoint URL ready, you're all set to make the connection. Follow these steps carefully.

Step 1: Get Data in Power BI

On the "Home" tab of the Power BI Desktop ribbon, click the Get Data button. A dropdown menu will appear. You can search here, but it's often easier to click "More..." at the bottom to open the full list of data connectors.

Step 2: Choose the SharePoint Online List Connector

In the Get Data window, use the search bar on the left and type "SharePoint." You’ll see several options. It's important to select the right one.

Choose SharePoint Online list and click Connect.

Step 3: Enter Your Site URL

A dialog box will appear asking for the Site URL. This is where you paste the URL you found earlier. After pasting the URL, click OK.

You may be prompted to sign in with your Microsoft 365 credentials. Go ahead and do this to authorize Power BI to access your SharePoint data.

Step 4: Select Your List from the Navigator

After a successful connection, the Navigator window will appear. This shows a list of all the lists and document libraries available on the SharePoint site you connected to. Find the name of your Microsoft List, check the box next to it, and a preview of the data will appear on the right.

Step 5: Transform Data (Highly Recommended)

At the bottom of the Navigator window, you have two options: "Load" and "Transform Data."

• Load: This option will immediately pull all the data from your list directly into your Power BI report, just as it is.
• Transform Data: This is the recommended choice. It opens the Power Query Editor, a powerful tool for cleaning and preparing your data before it loads into your report.

Click Transform Data. SharePoint lists contain a lot of hidden, system-related columns that you don't need for analysis. Taking a moment to clean your data will make building reports much easier.

Cleaning Your Data in Power Query

You're now in the Power Query Editor. This may look a little intimidating, but it's where the magic really happens. A few simple steps here can save you hours of headaches later.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

1. Remove Unnecessary Columns

Look at all the columns Power BI imported. You'll see your list columns (like "Task Name," "Status," "Due Date") mixed in with many others like "ContentTypeId," "GUID," "FileSystemObjectType." You don't need these system columns.

Hold the Ctrl key and click on the headers of the columns you want to keep. Once you've selected them, right-click on one of the selected headers and choose Remove Other Columns. This quickly gets rid of all the clutter.

2. Expand Complex Columns

Columns in Microsoft List like "Person" (e.g., Assigned To), "Choice," or "Lookup" don't import as simple text. They import as records or lists that you need to "expand" to see the useful information inside. These columns will have a header with a double-arrow icon.

Click the double-arrow icon on a column header (like "AssignedTo"). A menu will pop up showing the fields within that record, such as Title (the person's name), Email, ID, etc. Uncheck "Use original column name as prefix" and select the field you want (e.g., "Title"). Click OK.

The column will be replaced with the data you selected, giving you a clean column with just the text values you need.

3. Rename Columns and Set Data Types

Double-click any column header to rename it to something more user-friendly (e.g., rename "Title" to "Task Name").

Next, check the data type for each column. Power Query makes a good guess, but sometimes you need to adjust it. Just to the left of each column name is an icon representing the data type (e.g., 1.2 for Decimal, ABC for Text, a calendar for Date). Click the icon to change the type if needed. Getting your dates recognized as dates and numbers as numbers is critical for accurate calculations.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

4. Close and Apply

Once you are happy with the state of your data, click the Close & Apply button on the Home tab of the Power Query Editor. This closes the editor and loads your clean, prepared data into your Power BI report model.

Building Your First Visuals

Now for the fun part! With your data loaded, you can start building a report. On the right side of the screen, you'll see a "Data" pane with your list's fields and a "Visualizations" pane with chart types.

Let's create a couple of simple charts.

Create a Bar Chart: Tasks by Status

1. Click on an empty space on the report canvas.
2. In the Visualizations pane, click the icon for a stacked bar chart.
3. From your "Data" pane, drag the "Status" field into the Y-axis box in the Visualizations pane.
4. Drag the "Status" field again into the X-axis box. Power BI will automatically change this to "Count of Status."

And just like that, you have a chart showing you exactly how many items you have in each status category!

Create a Pie Chart: Tasks by Assignee

1. Click on another empty space on the report canvas.
2. In the Visualizations pane, click the icon for a pie chart.
3. Drag your "Assigned To" field (the one you expanded earlier) into the Legend box.
4. Drag your primary identifying field (like "Task Name") into the Values box. It will automatically convert to "Count of Task Name," breaking down the task distribution perfectly.

Now, try clicking a slice of the pie chart. Notice how the bar chart automatically filters to show only the data for the person you selected? That’s the interactivity that makes Power BI so effective.

Final Thoughts

By connecting your Microsoft List to Power BI, you've turned a simple data table into a powerful, interactive analytical tool. Following the steps to connect, clean the data in Power Query, and build visuals unlocks a new level of understanding from the information you're already tracking. Now you can easily monitor performance, identify bottlenecks, and share clear insights with your team just by refreshing your data.

Processes like these are powerful but can still feel manual, especially when you need to combine data from many different places. At Graphed, we've built a way to automate this entire workflow. Instead of using connectors and Power Query, you simply connect all your sales and marketing sources (like Google Analytics, Salesforce, Shopify, and more), and use plain English to ask for the dashboards and reports you need. We're on a mission to let you get back to making decisions, not wrangling data.