What is SharePoint in Power BI?

If you're using Power BI for your business reporting, odds are you're frequently working with data stored in Excel spreadsheets, CSV files, or simple lists. This article will show you how to use Microsoft SharePoint as a central hub for those files, making it one of the most practical and efficient data sources for your Power BI dashboards.

Graphed

Still Building Reports Manually?

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

Watch the Demo

What is SharePoint and Why Is It Great for Power BI?

At its core, SharePoint is a web-based platform used by organizations for collaboration. Think of it as a shared server in the cloud where teams can store, organize, and share documents and information. Many businesses that use Microsoft 365 already have access to SharePoint, often using it to host company intranets or manage team files.

So, why is this an ideal setup for Power BI? Storing your data source files in SharePoint instead of on individual computers creates a single source of truth. Here are the key benefits:

• Collaboration-Friendly: Multiple team members can access and update the source files (like a weekly sales report in Excel) without emailing different versions back and forth. Everyone works from the same document.
• Cloud-Based & Accessible: Your data lives in the cloud, meaning your Power BI reports can be refreshed automatically on a schedule from anywhere. You don't need to have a specific computer turned on and connected to the network for a data refresh to work.
• Version Control: SharePoint automatically keeps a history of changes to your files. If someone makes a mistake while updating a spreadsheet, you can easily revert to a previous version.
• Permissions Management: You can control who has permission to view or edit the source files, ensuring data security and integrity. Power BI respects these permissions during data refreshes.

In short, using SharePoint turns your individual data files into a robust, collaborative, and refreshable data source for Power BI, solving many common reporting headaches for teams.

Choose Your Connection: Files, Folders, or Lists?

Connecting Power BI to "SharePoint" isn't a single action. There are three primary ways to do it, depending on where and how your data is stored. Understanding the difference is crucial for picking the right method.

1. SharePoint File (Excel or CSV): This is the most common use case. You have a single Excel workbook or CSV file stored in a SharePoint document library that contains the data you want to visualize. This is perfect for budgets, project timelines, or specific marketing reports.
2. SharePoint Folder: Use this method when you have multiple files with the exact same structure stored in one folder. A classic example is having a separate sales report file for each month (Jan_Sales.xlsx, Feb_Sales.xlsx, etc.). Power BI can connect to the folder and automatically combine all the files into one single table.
3. SharePoint List: A SharePoint List is a simple, web-based table created directly within SharePoint, similar to a very basic spreadsheet. It’s useful for tracking simple data like team task lists, issue logs, or event registrations directly on your SharePoint site without using a separate file.

Next, we'll walk through the step-by-step process for each of these connection types.

Graphed

Still Building Reports Manually?

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

Watch the Demo

How to Connect Power BI to an Excel File in SharePoint

This is the most popular connection method. You have a single Excel file that one or more people on your team regularly update, and you want Power BI to always reflect the latest data.

The trickiest part is getting the correct URL. The URL you see in your browser's address bar won't work directly.

Step 1: Get the Correct File Path

First, navigate to your SharePoint document library and find the Excel file you want to connect to. Don't click to open it in an online viewer. Instead, follow these steps:

1. Click the three dots (...) next to the file name.
2. Select Details in the menu that appears.
3. A pane will open on the right side. Scroll all the way down and click the Copy path icon. This copies a clean URL to your clipboard.

Pro Tip: Another method is to open the file in the Excel Desktop App. Once opened, go to File > Info and click "Copy Path". This often gives you the cleaner URL you need.

Step 2: Clean Up the URL

The path you copied is almost perfect, but you need to make one small edit. Paste the URL into any text editor (like Notepad). It will look something like this:

https://yourcompany.sharepoint.com/sites/YourSite/Shared%20Documents/Folder/YourFile.xlsx?web=1

You need to delete everything from the question mark (?) onward. The clean URL you'll use in Power BI should simply end in .xlsx (or .csv for a CSV file). In this example, your final URL would be:

https://yourcompany.sharepoint.com/sites/YourSite/Shared%20Documents/Folder/YourFile.xlsx

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 3: Connect from Power BI Desktop

Now, open Power BI Desktop and start the connection process:

1. On the Home ribbon, click Get Data and select Web.
2. In the "From Web" dialog box, paste your cleaned-up URL into the text box and click OK.
3. Power BI will prompt you for authentication. Choose Organizational account from the options on the left.
4. Click Sign in and use your Microsoft 365 credentials (the same ones you use for SharePoint or Outlook).
5. After signing in, click Connect.
6. The Navigator window will appear, showing all the worksheets and Excel Tables found within your workbook. It is a best practice to select a formatted Excel Table (it will have a blue header icon) rather than a raw worksheet for a cleaner import.
7. Check the box next to the table you want, then choose either Load to bring it into your report or Transform Data to open the Power Query Editor for cleaning and shaping first.

That's it! Your Power BI report is now linked to your Excel file in the cloud. Any changes saved to that SharePoint file will be pulled into your report during the next data refresh.

How to Connect Power BI to a SharePoint Folder

This method saves a massive amount of time when you need to consolidate multiple files. Imagine you get a new CSV export from a tool every week, and you want to analyze trends over time without manually copying and pasting everything into one giant file.

Step 1: Get the SharePoint Site URL

For this method, you don't need the path to the specific folder. You just need the URL for the top-level SharePoint site. Navigate to the homepage of your SharePoint site and copy the URL from your browser's address bar. It should be a simple address like:

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

Step 2: Connect from Power BI Desktop

1. In Power BI Desktop, click Get Data and search for "SharePoint" in the search bar.
2. Select SharePoint Folder from the list and click Connect.
3. Paste the SharePoint site URL you copied in the previous step and click OK.
4. Power BI will show a preview of every single file on that SharePoint site. Click the Transform Data button to open the Power Query Editor — you'll need to filter this list down.

Step 3: Filter and Combine the Files

You're now in the Power Query Editor and need to tell Power BI which files to combine.

1. Use the filter controls on the "Folder Path" column to drill down and select only the specific folder that contains your files.
2. Once filtered, you'll see a column named "Content" with a button that has two downward arrows. This is the Combine Files button. Click it.
3. Power BI will ask you to select a sample file to determine the import steps. It uses the first file in the list by default to understand the structure (columns, data types, etc.).
4. Choose the worksheet or object to extract from the sample file (e.g., "Sheet1"). Power BI will show a preview. Click OK.
5. Power Query will then work its magic. It automatically generates the steps needed to open each file, extract the correct data, and append it all into a single table. It even adds a "Source.Name" column so you can see which file each row of data came from.

You can now load this combined table into your report. The best part? When you add a new file to that SharePoint folder (e.g., March's sales report), the next time you refresh your Power BI report, it will be automatically included in your analysis.

Graphed

Still Building Reports Manually?

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

Watch the Demo

How to Connect Power BI to a SharePoint List

SharePoint Lists are great for non-technical users to track simple data sets like customer feedback, event attendance, or project tasks without leaving the SharePoint environment.

Connecting is much more straightforward than with files.

1. In Power BI Desktop, click Get Data and search for "SharePoint".
2. Select SharePoint Online List and click Connect.
3. Paste the root URL of your SharePoint site (the same one you'd use for the Folder method, e.g., https://yourcompany.sharepoint.com/sites/YourSite/).
4. If prompted, choose the correct authentication method (usually V2.0 for Online Lists) and sign in with your organizational account.
5. The Navigator window will appear, showing all the SharePoint Lists available on that site. Check the box next to the one you want to import.
6. Again, you can select Load or Transform Data. It's often a good idea to transform list data, as SharePoint adds several metadata columns (like ‘Last Modified’, ‘Created By’, etc.) that you might want to remove.

Best Practices for a Smooth Experience

• Use Excel Tables: When working with Excel files, always format your data as a named Table (Select your data > Home > Format as Table). This gives you structured column headers and makes the import process in Power BI much cleaner and more reliable.
• Consistent Schemas: When using the SharePoint Folder connection, ensure every file has the exact same column names and order. Any deviation can cause the combine process to fail.
• Manage Permissions in One Place: Remember that Power BI relies on SharePoint permissions. If a user can’t access your final report, first check they have at least "Read" access to the underlying SharePoint file, folder, or list.
• Don't Move Your Files: Once you connect a file or folder to Power BI, avoid renaming or moving it. This will break the data source path in your Power BI query, and you’ll have to re-link it.

Final Thoughts

Connecting Power BI to your SharePoint files and lists automates your reporting by creating a stable, collaborative link to your source data. Whether you're consolidating monthly reports or linking to a team-managed budget file, this setup allows your team to focus more on analyzing insights rather than manually updating reports.

We know that creating reliable data connections is the foundation of any good dashboard. But data often lives in more than just SharePoint, it's spread across Google Analytics, Facebook Ads, Shopify, CRMs, and more. To help with that, we built Graphed to simplify the entire process. Instead of configuring each connection manually, you just link your accounts in a few clicks, and our AI can instantly build comprehensive, real-time dashboards for marketing and sales by asking simple questions in plain English.