How to Get Data from SharePoint to Power BI
A ton of your team's most valuable information - project plans, customer feedback, expense trackers, and content calendars - lives inside SharePoint. The problem is that while it's great for collaboration, SharePoint's native reporting features are quite basic. This article will walk you through, step-by-step, how to connect that SharePoint data directly to Power BI to build dynamic, insightful, and automated dashboards.
Why Connect SharePoint to Power BI?
Connecting SharePoint to Power BI isn't just a technical exercise, it's a strategic move to turn your collaborative information into a clear source of truth. SharePoint excels at storing team files and structured data in lists, and Power BI excels at visualizing data to reveal trends and insights. Bringing them together allows you to automate reporting, eliminate manual data entry, and give your team a live view of performance without ever leaving their workflow.
Imagine automatically updated dashboards that track:
- Project milestones and task statuses from a SharePoint list.
- Sales team performance from data collected in Excel files within a document library.
- Marketing campaign results from a centralized tracker updated by multiple team members.
By building this connection, you stop spending hours downloading files and wrangling spreadsheets and start spending that time making decisions based on real-time data.
Before You Connect: A Quick Prep Step
Before you jump into Power BI, take a moment to look at your data's structure in SharePoint. A little preparation here can save a lot of headaches later. You'll typically be connecting to one of two data types:
- SharePoint Lists: These are ideal. They function like simple databases or well-structured spreadsheets. Make sure your column names are clear (e.g., 'Project Name' instead of 'column1') and that the data within each column is consistent (e.g., the 'Status' column only contains values like 'Not Started', 'In Progress', and 'Complete').
- Files in a Document Library (Excel, CSV): This is a very common scenario. If you’re pulling from files like Excel workbooks, ensure they share a consistent structure. This means the columns should be in the same order with the same headers in every file you want to combine. Name your file tabs logically if you're using Excel!
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.
Connecting Power BI to a SharePoint List (Step-by-Step)
This is the most direct and common method for creating reports from structured data your team manages in SharePoint. Follow these steps carefully.
1. Get Data in Power BI
First, open a blank report in Power BI Desktop. In the "Home" tab of the ribbon, click the Get Data button. From the dropdown, you can either select SharePoint Online List if you see it, or click More... to open the full connector library.
2. Search for the SharePoint Connector
In the "Get Data" pop-up window, type "SharePoint" into the search bar. You'll see several options. For this method, select SharePoint Online List and click Connect.
3. Enter Your SharePoint Site URL
This next step trips up a lot of people. Power BI is not asking for the URL of the specific list you want to connect to. It needs the URL for your main SharePoint site.
For example, if your list URL is:
https://yourcompany.sharepoint.com/sites/Marketing/Lists/CampaignTracker/AllItems.aspx
You only need to enter the root site URL:
https://yourcompany.sharepoint.com/sites/Marketing
Copy and paste that base URL into the "Site URL" field and click OK.
4. Authenticate Your Account
Power BI will now ask you to sign in. Since you're connecting to SharePoint Online, select "Microsoft Account" from the left-hand menu, click Sign In, and use your regular company Office 365 credentials.
5. Select Your List(s) from the Navigator
Once you’ve successfully authenticated, a "Navigator" window will appear. It will show you a list of every single list available on the SharePoint site you connected to. Find the list you need (e.g., 'CampaignTracker'), check the box next to it, and a preview of its data will show on the right.
6. Load or Transform Your Data
At the bottom right of the Navigator, you'll see two options: Load and Transform Data.
- Load: This option will immediately import the data as-is directly into your Power BI data model. This is fine for exceptionally clean data, but it's rarely the best choice.
- Transform Data: This is almost always the correct option. Clicking it opens the Power Query Editor, a powerful tool for cleaning, shaping, and preparing your data before it ever hits your report. You can remove unnecessary columns, change data types (like making sure a 'Date' column is actually a date format), and filter out irrelevant rows. Take the extra minute to explore this option. It builds better, faster, more reliable reports.
Once your data is cleaned in the Power Query Editor, simply click 'Close & Apply' in the top-left corner, and you're ready to start building visuals with your SharePoint list data!
Connecting to Files in a SharePoint Folder
What if your data is in a set of Excel files that your team drops into a SharePoint document library each week or month? Power BI can handle that too, combining them into a single, clean table.
1. Choose the SharePoint Folder Connector
Back in Power BI Desktop, navigate to Get Data > More... and search for "SharePoint". This time, select the SharePoint Folder connector.
2. Enter the Site URL (Again!)
Just like with SharePoint Lists, paste the root URL of your SharePoint site here, not the full path to the document library. For example, use https://yourcompany.sharepoint.com/sites/SalesTeam and click OK.
3. Transform the File List
After you authenticate, a window will appear showing a list of every single file in that entire SharePoint site. Do not just click "Combine" or "Load". You must click Transform Data to open the Power Query Editor.
4. Filter to Your Specific Folder and Files
In the Power Query Editor, you'll see metadata for all the files, including their names, extensions, and folder paths. Your first job is to filter this list down to just the files you need.
- Use the filter on the 'Folder Path' column to select the specific document library and folder where your files are stored.
- If necessary, add another filter on the 'Extension' column to only include '.xlsx' or '.csv' files.
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.
5. Combine Your Files
Once you've filtered the list to show the files you want to merge (e.g., 'January Sales.xlsx', 'February Sales.xlsx'), find the column named 'Content' and click the 'Combine Files' icon (it looks like two downward-facing arrows).
Power BI will then ask you to provide a sample file to understand the structure. Select a file, and then choose the specific table or sheet (e.g., 'Sheet1') you want to extract from each workbook. Power BI will automatically create a function to repeat this process for every file in your filtered list and append them all into one master table. It's an incredibly powerful feature for automating reports.
Common Issues and Best Practices
- Check Your Permissions: You need at least "Read" permissions on the SharePoint site and the specific list or library you're trying to connect to. If you get a credential error, this is the first thing to check.
- Fix the URL mistake: The single most common error is a user pasting the full, lengthy URL to a list. Remember, Power BI wants the short, base URL of the site.
- Data Refresh: After you publish your report to the Power BI Service (app.powerbi.com), you'll need to configure a data refresh. Go to the dataset's settings, enter your credentials again, and schedule a recurring refresh (e.g., daily at 8 AM). This ensures your dashboard always shows the latest data from SharePoint automatically.
Final Thoughts
Connecting data from a SharePoint list or folder to Power BI is a fantastic way to automate your team's reporting and turn static collaboration data into actionable insights. While it involves several steps, from getting the URL right to shaping data in Power Query, mastering this process gives you a repeatable workflow for a huge range of business needs.
We built Graphed because we believe getting insights shouldn't require a steep learning curve or numerous configuration steps. While tools like Power BI are powerful, they are often complex for the marketing and sales teams who need answers now. We provide simple, one-click connections to your core data sources, allowing you to build dashboards by simply describing what you want in plain English. For anyone looking to bypass the technical setup and get straight to data conversation, it's a completely new way to work.
Related Articles
How to Create Facebook Ads API for Claude Code
Create a Meta Marketing API access token that never expires, using a system user, so Claude Code can pull Facebook ad reports and manage campaigns.
ClickHouse vs Postgres: When to Switch to Columnar
Comparing ClickHouse vs PostgreSQL means matching storage architecture to workload. Row storage wins for transactions. Columnar wins for scan-heavy analytics.
Google Ads for Orthodontists: Get More Booked Consults
Most orthodontic Google Ads campaigns waste money on low-intent searches. The problem is structure, not the platform.