Can Power BI Pull Data from SharePoint?

Wondering if you can connect your SharePoint data to Power BI? Yes, you absolutely can, and it’s one of the best ways to transform your team's collaborative data into powerful, automated reports. This article will show you exactly how to pull data from SharePoint lists, folders, and even specific Excel files, turning your SharePoint site into a dynamic data source for your dashboards.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Why Connect Power BI and SharePoint in the First Place?

If your team uses SharePoint for project-tracking lists, document storage, or as a central hub for information, you're sitting on a valuable data source. By connecting it to Power BI, you unlock several key benefits:

• Automate Reporting: Stop manually downloading files and updating spreadsheets. Once connected, your Power BI reports can be scheduled to refresh automatically, always showing the latest data from your SharePoint list or folder.
• Create Interactive Dashboards: Transform a static SharePoint list into a fully interactive dashboard. Users can filter by project manager, drill down into task statuses, or see timelines visually instead of scanning rows of text.
• Unify Your Data: You might track project tasks in a SharePoint list and budget information in an Excel file stored in a document library. In Power BI, you can pull data from both sources and link them together to get a complete view of your project's health.
• Leverage Powerful Analytics: SharePoint is great for storing data, but Power BI is built for analyzing it. Once the data is in Power BI, you can use DAX (Data Analysis Expressions) to create complex calculations, forecast trends, and find insights that would be nearly impossible to spot in a simple SharePoint view.

The Most Important First Step: Getting the Right SharePoint URL

Before you connect to anything, you need to understand the difference between the full browser URL and the root SharePoint site URL. This is the #1 place where people run into connection issues. Power BI needs the clean, top-level URL for your site - not the direct link to a specific list or view.

For example, you might navigate to a project tracker list and see this URL in your browser:

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

The root site URL that Power BI needs is everything before the /Lists part:

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

Simply copy this shorter, cleaner URL. You'll use this root URL for connecting to any list, folder, or file within that specific SharePoint site.

Graphed

Still Building Reports Manually?

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

Watch the Demo

How to Pull Data from a SharePoint List

SharePoint lists are fantastic for structured data like issue trackers, contact lists, event schedules, or simple project plans. Connecting one to Power BI lets you visualize that data in meaningful ways.

Step-by-Step Instructions

1. Get Data: In Power BI Desktop, go to the Home tab, click Get Data, then select More....
2. Select Connector: In the pop-up window, type "SharePoint" into the search bar. Select SharePoint Online List from the results and click Connect.
3. Enter Site URL: A new dialog box will appear. This is where you paste the root site URL you copied in the previous step. Leave the "Implementation" option at 2.0 unless you have a specific reason to use the older version. Click OK.
4. Authenticate: You will be prompted to sign in. Select Microsoft account on the left, click Sign in, and enter the Office 365 credentials you use to access SharePoint.
5. Choose Your List: After successful authentication, the Navigator window will appear, displaying all the lists and libraries in that SharePoint site. Find and check the box next to the SharePoint list you want to connect to. You'll see a preview of its data on the right.
6. Load or Transform: You now have two options:

Working with List Data in Power Query

SharePoint lists include many hidden system columns used for internal tracking (like 'ContentTypeId', 'GUID', etc.). In Power Query, you should remove these to make your model tidy. Simply select the columns you don't need, right-click, and choose Remove Columns.

If your list uses look-up fields to connect to other lists, they may load into Power Query as a [Record] or [List]. Click the two-way arrows on the column header to expand them and pull in the specific fields you need.

How to Pull Data from a SharePoint Folder

Connecting to a folder is a game-changer if you have multiple files with the same format. Imagine getting a monthly sales report as a new CSV or Excel file each month. By connecting to the folder where they're stored, Power BI can automatically combine them into a single table.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step-by-Step Instructions

1. Get Data: In Power BI, go to Get Data > More... and search for SharePoint Folder. Click Connect.
2. Enter URL: Once again, paste your root site URL and click OK.
3. Authenticate: Sign in with your Microsoft account if prompted.
4. Initial File View: You will now see a list of all files and folders within that entire SharePoint site collection. This view can be overwhelming, so it's critical to click Transform Data to go to the Power Query Editor.
5. Filter to Your Folder: In Power Query, use the filter on the Folder Path column to drill down to the exact folder containing your files.
6. Combine Files: Once you've isolated the files you want to combine, locate the Content column. Click on the Combine Files icon (a double-down arrow) in the header of that column.
7. Configure Combination: Power BI will then show you a preview based on the first file and ask you to select the sheet or object to use. Make your selection and click OK. Power BI automatically generates the queries and functions needed to loop through each file, extract the specified data, and stack it all into one clean table.

The key here is consistency. For "combine files" to work smoothly, all your files in the folder should have the exact same structure (same column names, same order, same data types).

How to Connect to a Single Excel or CSV File

What if you just want to connect to a single Excel workbook that your team updates regularly in SharePoint, like a shared budget tracker? It's easy, but the method might surprise you.

Resist the temptation to use the 'Web' connector. While it can work sometimes, it often causes authentication problems. The most reliable method is to use the SharePoint Folder connector.

The Best Method for a Single File

1. Start with the Folder Connector: Follow steps 1-4 from the 'How to Pull Data from a SharePoint Folder' section above. Use the SharePoint Folder connector and head straight to the Power Query Editor (Transform Data).
2. Navigate to Your File: In Power Query, use the filter controls on the 'Folder Path' and 'Name' columns to find the single row representing the Excel file you want.
3. Click on "Binary": Do not click the "Combine Files" icon this time. Instead, in the row for your file, simply click on the word Binary in the Content column.
4. Import Data: Power BI will now "open" that binary content as an Excel file. A new Navigator pane will appear, just like when you connect to a local Excel file, showing you all the sheets and tables inside the workbook.
5. Select and OK: Choose the table or sheet you need, and the data will be loaded into your query.

With this connection, you can edit the Excel file in SharePoint (or Teams, since the files are often linked), and simply hit the Refresh button in Power BI to see the latest updates.

Troubleshooting Common Issues

"Access to resource is forbidden" / "Unable to connect" Errors

Ninety-nine percent of the time, this is a permissions issue. First, check if you can access the SharePoint site in your browser with the same credentials. If that works, the problem is likely saved credentials within Power BI. Go to File > Options and settings > Data source settings. Find the entry for your SharePoint site, select it, and click Clear Permissions. Then, try connecting again - it will force you to re-authenticate from scratch.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Scheduled Refresh Fails in Power BI Service

If your desktop refreshes fine but your published dashboard fails to refresh on PowerBI.com, you need to configure your data source credentials in the service. Go to your workspace, find your dataset, click the ellipses (...) and go to Settings. Expand the Data source credentials section, click Edit credentials for your SharePoint source, and sign in again using your Microsoft account. Use OAuth2 as the authentication method to ensure the Power BI service can properly connect on your behalf.

Final Thoughts

Connecting Power BI to SharePoint opens up a ton of possibilities for creating automated and dynamic reports from data that lives outside of formal databases. Now that you know how to tap into lists, folders, and individual files, you can turn your team's everyday work into a powerful source for insights.

Once you’ve mastered connecting your operational data, a whole new world of analysis can open up, especially for tracking marketing and sales performance across digital platforms. For that, we built Graphed to simplify the process entirely. Instead of configuring data sources and building reports manually, we enable you to unify sources like Google Analytics, HubSpot, and Facebook Ads in seconds and create dashboards just by describing what you want to see - all using natural language a lot like a conversation.