How to Link a SharePoint List to Power BI

Tired of manually exporting your SharePoint lists to Excel just to build a simple chart? There's a much better way. By directly linking your SharePoint list to Power BI, you can turn that static data into a dynamic, automatically refreshing dashboard. This article provides a step-by-step guide to connecting your SharePoint list to Power BI, including practical tips for cleaning your data and avoiding common pitfalls.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Connect a SharePoint List to Power BI?

If you're using SharePoint lists to track projects, manage inventory, log customer feedback, or handle any other kind of structured data, you're sitting on a valuable resource. The problem is that the default SharePoint list view isn't great for analysis. It's hard to spot trends, compare performance over time, or share high-level insights with stakeholders.

Connecting your list to Power BI unlocks a new level of analysis. Instead of flat, boring rows of data, you can:

• Create Interactive Visualizations: Build live charts, graphs, maps, and tables that users can filter and explore in real time. Turn a project tracking list into a Gantt chart or a sales log into a performance dashboard.
• Automate Your Reporting: Set up your Power BI dashboard to automatically refresh its data from the SharePoint list on a schedule. No more downloading CSVs every Monday morning. The report you build today will still be accurate next month.
• Combine Data Sources: Pull in data from your SharePoint list and merge it with data from other sources, like Google Analytics, Salesforce, or an Excel file, to get a complete picture of your performance.
• Share Professional Insights: Embed live dashboards in Microsoft Teams, share them via a secure link, or set up email subscriptions for your team.

In short, it allows you to move from tedious data collection to creating meaningful, actionable insights.

Before You Start: What You'll Need

The process is straightforward, but you'll have a much easier time if you have these three things ready before you begin:

1. Power BI Desktop: This is the free application from Microsoft where you'll build your report. If you don't have it installed yet, you can download it from the Microsoft Store.
2. SharePoint Site Access: You need to have at least "Read" permissions for the SharePoint site and the specific list you want to connect to. If you can view the list in your browser, you should be fine.
3. The SharePoint Site URL: This is the most common place where people get tripped up. You don't need the URL of the list itself, you need the URL of the main SharePoint site that contains the list.

How to Find Your SharePoint Site URL

Finding the right URL is crucial. Go to your SharePoint list in a web browser. Now, look at the URL in the address bar. It will probably look something long and complicated like this:

https://yourcompany.sharepoint.com/sites/Marketing/Lists/CampaignTracker/AllItems.aspx

You need to ignore everything from /Lists/ onward. The part you need is the root site URL, which in this example would be:

https://yourcompany.sharepoint.com/sites/Marketing

Copy this part of the URL and have it ready. This simple step will save you a lot of frustration.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

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

With Power BI Desktop open and your Site URL handy, you're ready to connect. Just follow these steps.

Step 1: Open Power BI and Select 'Get Data'

On the "Home" ribbon in Power BI Desktop, you'll see a prominent button labeled Get Data. Click on this to open a menu of common data sources. If you don't see SharePoint listed, click on More... at the bottom of the list to open the full data source window.

Step 2: Choose the SharePoint Online List Connector

In the "Get Data" window, you'll see a search bar in the top-left corner. Type "SharePoint" into the search bar to filter the long list of available connectors.

You will see a few options. The one you want for this task is SharePoint Online List. Select it and click the Connect button.

Step 3: Enter Your SharePoint Site URL

Now, a dialog box will appear asking for your SharePoint Site URL. This is where you paste the root site URL you identified earlier. Remember, don’t use the full URL to the specific list itself.

You will also see an "Implementation" option, 1.0 and 2.0 (Beta). For most standard lists, the 1.0 version is stable and works perfectly fine. The 2.0 version uses a different API and can be helpful for very large lists, but let's stick with 1.0 for now. Click OK.

Step 4: Authenticate Your Account

Because you're connecting to a secure online service, Power BI will need you to sign in. In the next window, select Microsoft Account on the left, click the Sign in button, and enter your work or school credentials (the same ones you use to access SharePoint).

After you successfully sign in, you’ll see a confirmation. Click the Connect button at the bottom right.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 5: Navigate and Select Your List

After connecting, Power BI will display a "Navigator" window. This window shows all the lists and other items available on the SharePoint site you connected to. Scroll through the list - it's usually alphabetical - or use the search bar to find the specific list you want to analyze.

Once you click on the list name, you'll see a preview of the data on the right. This helps you confirm you've selected the correct one. Click the checkbox next to your list name.

Step 6: Choose 'Transform Data' (Highly Recommended)

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

• Load: This will pull all columns and rows from your SharePoint list directly into your Power BI data model as-is.
• Transform Data: This will open the Power Query Editor, a powerful tool for cleaning, shaping, and preparing your data before it's loaded into your final report.

While 'Load' seems quicker, always choose Transform Data. SharePoint lists contain a lot of hidden, system-generated columns that you don't need for your analysis ('Content Type ID', 'GUID', 'Attachments', etc.). Loading all this clutter will slow down your report and make it harder to work with. Clicking 'Transform Data' is a critical best practice.

Cleaning Your Data in Power Query

After clicking 'Transform Data', the Power Query Editor will open. This is where you get your data report-ready. A few minutes spent here will save you hours of frustration later.

1. Remove Unnecessary Columns

Your preview will likely show dozens of columns. Using the Choose Columns button on the "Home" ribbon, you can easily select only the data you actually need. Uncheck all the clutter and keep just the columns relevant to your analysis, such as 'Title', 'Status', 'AssignedTo', 'Created Date', 'Project Budget', etc.

2. Expand Record and List Columns

Columns that refer to people ('Created By', 'Assigned To'), choices, or lookups will often appear as '[Record]' or '[List]' instead of the actual names. Don't panic, this is normal.

Click the two-way arrow icon in the header of that column. This will bring up a menu to "expand" the record and pull out the specific fields you need. For a person column, for example, you can choose to expand and show their 'Title' (name) or 'Email'. This is how you go from a useless ID to the actual name of the project manager.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

3. Check Your Data Types

Power Query is pretty good at guessing data types, but it's always worth double-checking. Make sure number columns are formatted as "Whole Number" or "Decimal Number", date columns are set to "Date" or "Date/Time", and text columns are "Text". You can change the data type by clicking the icon (e.g., 'ABC' for text, '123' for number) in the column header.

4. Close & Apply

Once you've cleaned everything up, click the Close & Apply button in the top-left corner of the Power Query Editor. This will apply all your transformation steps and load the clean, ready-to-use data into your Power BI report.

That's it! Your SharePoint data is now in Power BI. You can head over to the 'Report' view and start building visuals by dragging and dropping your fields onto the canvas.

Final Thoughts

Connecting your SharePoint list to Power BI transforms it from a simple data repository into a powerful, automated reporting engine. By following these steps, you can set up a direct link, schedule automatic data refreshes, and create the kind of interactive, data-driven reports that help non-technical stakeholders make better decisions.

While using Power BI is a great way to build in-depth reports, the process still involves learning new software, transforming your data, and manually building visuals. For teams that want answers fast without the setup time, we created Graphed. We connect directly to hundreds of data sources - like Google Analytics, Salesforce, and ad platforms - and let you create real-time dashboards and reports simply by asking for what you need in plain English. This turns hours of pulling reports into a 30-second conversation, so you can focus on insights instead of configuration.