How to Link Power BI to SharePoint

Putting your important business data in SharePoint is a great first step, but that's only half the battle. To turn that raw data into meaningful insights, you need to visualize it, and for that, Power BI is the perfect Microsoft tool. This article will walk you through exactly how to connect your Power BI reports directly to your SharePoint data, covering both SharePoint Lists and files like Excel or CSVs.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Why Pull SharePoint Data into Power BI?

Before we jump into the steps, let's quickly cover why you'd want to do this in the first place. When you directly link SharePoint to Power BI, you create a single source of truth for your data. No more downloading CSV files every Monday morning, saving new versions, and worrying if your report is based on outdated information. A direct connection allows you to:

• Automate Refreshes: Set up a schedule in the Power BI service to automatically refresh your data, ensuring your dashboards are always current.
• Create Robust Dashboards: Transform a simple SharePoint list into interactive charts, graphs, and KPIs that tell a compelling story.
• Combine Data Sources: Pull in data from a SharePoint list, an Excel file in another SharePoint folder, and your CRM to build a comprehensive view of your entire business.

What You'll Need Before You Start

To make this process as smooth as possible, make sure you have these three things ready to go:

• Power BI Desktop: This is the free application from Microsoft where you'll build your reports. Make sure it's installed on your computer.
• Access to SharePoint: You need permission to view the SharePoint site and the specific list or file you want to connect to. Typically, "Read" access is sufficient.
• Your SharePoint Site URL: You'll need the main URL for your SharePoint site. We’ll show you exactly how to find the right one below.

Connecting Power BI to a SharePoint List

SharePoint lists are a fantastic way to store structured data, like project tasks, sales leads, or inventory logs. Hooking a SharePoint List up to Power BI is the most common use case, and it's surprisingly straightforward.

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

Launch Power BI Desktop. On the main Home tab in the ribbon, click the Get Data button. A new window will pop up with a long list of potential data sources.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 2: Find the SharePoint Connector

In the "Get Data" window, type "SharePoint" into the search bar. You will see a few options. For this task, select SharePoint Online List and click Connect.

Step 3: Enter Your SharePoint Site URL

Here’s a detail where many people get stuck. Power BI is not asking for the full URL to your specific SharePoint list. Instead, it wants the root URL of the site where the list lives.

For example, if the full browser URL for your list is https://yourcompany.sharepoint.com/sites/Marketing/Lists/CampaignTracker/AllItems.aspx, you only need the first part:

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

Copy that root site URL and paste it into the "Site URL" field in Power BI, then click OK.

Step 4: Authenticate Your Account

Power BI will likely prompt you to sign in. In the left-hand pane, select Microsoft Account, click the Sign in button, and enter the same credentials you use to log in to SharePoint (your Microsoft 365 account). After signing in, click Connect.

Step 5: Select Your SharePoint List

After a moment, a Navigator window will appear, showing you all the lists available on the SharePoint site you connected to. Find the name of the list you want to use, check the box next to it, and you'll see a preview of the data on the right.

Step 6: Transform Your Data

At the bottom right of the Navigator window, you'll see two options: Load and Transform Data. While you might be tempted to hit "Load," it's almost always a better idea to click Transform Data first. This will open the Power Query Editor, which is a powerful tool for cleaning and preparing your data (like removing unnecessary columns, changing data types, or splitting text) before building your visuals.

Once you've cleaned everything up in Power Query, click Close & Apply in the top left corner, and your SharePoint data will be loaded into your Power BI report, ready for visualization.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Connecting Power BI to a File in SharePoint (Like an Excel or CSV)

What if your data isn't in a SharePoint list, but in an Excel or CSV file stored in a SharePoint document library? The approach is a little different, and there’s a common mistake you want to avoid.

Common Mistake: It seems logical to use the "Excel Workbook" or "Text/CSV" connectors in Power BI. However, these are designed for local files and will cause authentication and refresh problems when pointed at a SharePoint URL. The correct way is to use the SharePoint Folder connector.

Step 1: Use the 'SharePoint Folder' Connector

In Power BI Desktop, go to Get Data again. This time, search for and select SharePoint folder and click Connect.

Step 2: Enter the Site URL

Just like before, enter the root URL of the SharePoint site where your file is stored (e.g., https://yourcompany.sharepoint.com/sites/Finance). Click OK.

Step 3: Finding Your File

After you connect, Power Query will open and show you a list of all files and folders within that SharePoint site. This can look overwhelming at first. You'll need to filter this list to find the specific file you're interested in.

You can use the drop-down filter arrows on the Folder Path or Name columns to narrow down the list until you've isolated the single file you want to use. For example, you might apply a text filter on the "Name" column for "sales-data-q3.xlsx".

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 4: Combine the File Content

Once you've filtered the list to your single target file, look for the column named Content. In the header of that column, you'll see an icon with two downward-facing arrows. This is the Combine Files button.

Click that icon. Power Query will walk you through a quick process to evaluate the file and show you the available sheets or tables within it. Select the worksheet you need (e.g., "Sheet1"), click OK, and Power Query will automatically extract the data from your Excel file and load it as a new table, ready for you to work with.

Tips for a Smooth Connection

Keep these best practices in mind to avoid common headaches when working with SharePoint data in Power BI.

• Permissions Are Everything: If you get an access denied error, check your permissions. You need at least "Read" access to both the SharePoint site and the list or file itself. Your Power BI refreshes will use the credentials you set, so if your permissions get removed, your refreshes will fail.
• Clean Data in Power Query: SharePoint lists often come with many system-generated columns that you don’t need (like ComplianceAssetId, OData__UIVersionString). Use Power Query to remove these extra columns. A cleaner data model is faster and easier to work with.
• Set Up Scheduled Refresh: The real power is in automation. Once your report is published to the Power BI Service (Power BI's online version), go to the dataset settings. Under Data source credentials, you'll need to re-enter your SharePoint credentials. Then you can enable Scheduled refresh and have your report update automatically daily or weekly.

Final Thoughts

Connecting Power BI directly to your SharePoint sources is an excellent way to move from manual, static reporting to an automated, reliable analytics system. By following the right steps to connect to SharePoint Lists or files in a folder, you can create a seamless data pipeline that keeps your dashboards alive with fresh, up-to-date information.

If dealing with connectors, data cleaning in Power Query, and setting up refreshes still feels like a lot of work, that’s because it sometimes is. At Graphed we built a tool that handles all this complexity for you. We simplify the entire process by letting you connect your data sources in seconds and then use simple, natural language to build the dashboard you need. Instead of clicking through menus and transforming columns, you can just ask, “Show me total project budget versus actual spend from our SharePoint list,” and get an interactive chart in real-time. It's the easiest way to get from data to insight.