How to Use SharePoint List in Power BI

Connecting a SharePoint list to Power BI turns your standard data-tracking into a dynamic, interactive dashboard that tells a story. It’s one of the easiest ways to move beyond static views and give your team real-time insights into project statuses, sales pipelines, or inventory levels. This tutorial will walk you through exactly how to connect your SharePoint list, clean up the data, and build your first visual report.

Why Use Power BI with Your SharePoint List?

While SharePoint lists are great for capturing and organizing information collaboratively, their built-in reporting features are limited. You can create different views and sort or filter data, but it's hard to get a high-level, visual overview. That's where Power BI comes in.

Here’s what you gain by making the connection:

• Interactive Visualizations: Instead of rows of text, you get filterable charts, graphs, and cards that help you spot trends instantly. Imagine clicking on a team member’s name and seeing all their assigned tasks light up across multiple charts.
• Data Mashups: You can pull in data from other sources - like an Excel file of project budgets or a Salesforce report on client engagement - and combine it with your SharePoint list data. This gives you a complete picture that no single system can provide.
• Automated Data Refreshes: Set it up once, and Power BI can automatically refresh the data from your SharePoint list on a schedule (e.g., daily or hourly). Your reports are always up-to-date without you ever having to manually export a CSV again.
• Sharing and Collaboration: You can publish your Power BI report and create a dashboard that is easily shared with stakeholders, keeping everyone on the same page with the latest data.

Getting Started: What You’ll Need

Before you dive in, make sure you have a couple of things ready. The good news is that you don't need a paid Power BI license to get started with this.

• Power BI Desktop: This is a free Windows application from Microsoft that you use to build reports. If you don’t have it, you can download it directly from the Microsoft Store.
• Access to SharePoint: You’ll need at least "Read" permissions for the SharePoint site and the specific list you want to connect to.
• The SharePoint Site URL: You'll need the main URL for your SharePoint site. This is not the URL of the list itself, but the root site where the list is hosted. It typically looks something like https://yourcompany.sharepoint.com/sites/YourSiteName.

Step-by-Step Guide: Connecting SharePoint to Power BI

Once you have everything ready, you can connect your data source. The process is straightforward, but pay close attention to the URL you use in step 3.

1. Open Power BI and Get Data

Launch Power BI Desktop. On the Home ribbon at the top, click on the "Get Data" button. If you don't immediately see SharePoint in the initial list, click "More..." to open the full list of data connectors.

2. Find the SharePoint Online List Connector

In the Get Data window, type "SharePoint" into the search bar. This will filter the list of connectors for you. Select "SharePoint Online List" and click "Connect".

Note: Make sure you select "SharePoint Online List" and not "SharePoint Folder" or "SharePoint List" - the latter is for older, on-premise versions of SharePoint.

3. Enter Your SharePoint Site URL

This is the most critical step. Power BI will ask for a "Site URL". You need to enter the URL of the main SharePoint site, not the full address of the list itself. For example, if your list is at https://acmecorp.sharepoint.com/sites/Marketing/Lists/CampaignTracker, you should only enter https://acmecorp.sharepoint.com/sites/Marketing.

Using the full list URL is a common mistake that will result in an error. Stick to the root site URL, and Power BI will find all the lists within it.

4. Authenticate Your Account

Next, Power BI will prompt you to sign in. Select "Microsoft Account" and click "Sign In". Use your regular company email and password that you use to log into Microsoft 365 and SharePoint. This authenticates your access and ensures you can only pull data you have permission to see.

5. Select Your List and Open Power Query

After you successfully sign in, a "Navigator" window will pop up, showing all the available lists on that SharePoint site. Find and check the box next to the SharePoint list you want to use. You'll see a small preview of the data on the right.

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

• Load: This will pull the data directly into your Power BI report as-is. This is rarely a good idea with SharePoint lists because they contain a lot of hidden, system-generated columns you don’t need.
• Transform Data: This is the recommended choice. Clicking it opens the Power Query Editor, a powerful tool for cleaning, shaping, and preparing your data before building reports with it.

Always choose "Transform Data".

Cleaning and Transforming Your Data in Power Query

The Power Query Editor is where you refine your raw data into a clean, usable dataset. SharePoint lists, by default, include dozens of internal columns that are helpful for SharePoint but are just noise for reporting. Here are the essential steps for cleaning your list data.

Remove Unnecessary Columns

Your first step is to get rid of the clutter. SharePoint adds columns like "FileSystemObjectType", "ServerRedirectedEmbedUri", "ContentTypeId", and many more that are irrelevant for your report. Scroll through your columns and select the ones you don’t need by holding down the Ctrl key and clicking their headers. Once selected, right-click any of the highlighted headers and choose "Remove Columns".

A more efficient way is to use the "Choose Columns" feature on the Home ribbon. This opens a dialog box where you can simply uncheck all and then re-check only the columns you want to keep, like "Title", "Status", "DueDate", "AssignedTo", etc.

Expand Record and List Columns

Certain types of SharePoint columns - like "Person or Group" (e.g., AssignedTo), "Lookup", or "Choice" - don't show up as simple text. Instead, they appear as a [Record] or [List]. To get the actual data, you need to expand them.

Look for a column header with a small "expand" icon (two arrows pointing in opposite directions). Click this icon, and a dropdown will appear with the fields inside that record. For example, expanding an 'AssignedTo' column might let you choose 'Title' (the person’s name) and 'Email'. Uncheck anything you don't need, clear the "Use original column name as prefix" checkbox for cleaner names, and click OK. The useful data will now appear in its own column.

Change Data Types

Power Query is usually good at guessing data types, but it's important to verify. A column containing numbers might be accidentally formatted as text, or a date column could be imported incorrectly. Look at the small icon next to each column header (like 'ABC' for text, '123' for whole numbers, or a calendar for dates).

If a data type is wrong, click the icon and select the correct type from the dropdown. This is essential for performing accurate calculations (like sums or averages) and sorting dates correctly.

When you're finished with your cleanup, click the "Close &amp, Apply" button in the top-left corner of the Power Query Editor. This applies all your changes and loads the cleaned data into your Power BI model.

Building Your First Report From SharePoint Data

With your clean dataset loaded, you're ready to start visualizing. The Power BI canvas is intuitive. The main panes you’ll use are:

• Fields pane (right): Shows all the columns from your SharePoint list dataset.
• Visualizations pane (right): This is where you choose the type of chart or visual (e.g., bar chart, pie chart, table).
• Canvas (center): The main area where you drag and drop fields to build your visuals.

Example: Creating a Project Task Dashboard

Let's pretend your SharePoint list tracks project tasks. Here are a few simple visuals you can create in minutes:

1. Total Task Count Card: From the Visualizations pane, select the "Card" visual. From your Fields pane, drag your main task ID or Title field into the "Fields" area of the Card visual. Click the dropdown on the field and select "Count" to get a high-level number of total tasks.
2. Tasks by Status Bar Chart: Select the "Stacked bar chart" visual. Drag the 'Status' column to the Y-axis and the 'Task ID' column (or another unique identifier) to the X-axis. Just like with the card, make sure the task ID is set to "Count". You now have a clear chart showing how many tasks are Not Started, In Progress, and Completed.
3. Filter by Assigned Person (Slicer): Click on a blank part of the canvas, then select the "Slicer" visual. Drag your 'AssignedTo' field into the slicer's "Field" box. You now have an interactive list of team members. Clicking on a name will dynamically filter the entire report to show only their tasks.

By combining just these three visuals, you've already created an interactive dashboard that's far more insightful than a raw SharePoint list.

Final Thoughts

You've successfully connected your SharePoint list to Power BI, transforming raw data into a powerful, interactive report that automatically updates. This process unlocks the potential of the data you're already collecting, providing your team with clear, actionable insights to make better decisions every day.

While building reports in Power BI is a classic way to level up your analytics, it still requires understanding how to connect sources, clean data using Power Query, and design visualizations. At Graphed , we streamline this entire workflow. Simply connect your marketing and sales data sources - from Google Analytics to Shopify to Salesforce - and then describe the report you need in simple, plain English. Our AI-powered tool does the heavy lifting, instantly generating real-time dashboards so you can jump from data to decision in seconds, not hours.