How to Connect Power BI to SharePoint

Microsoft SharePoint is widely used by organizations to store documents, manage lists, collaborate on projects, and organize business information.

Connecting SharePoint to Power BI allows users to bring SharePoint lists and files into Power BI for analysis and reporting. This can be useful for tracking projects, managing tasks, analyzing business records, monitoring documents, and creating reports from files stored in SharePoint.

Power BI provides different ways to work with SharePoint data. The appropriate connector depends on the type of information you want to analyze:

• SharePoint Online List — for information stored in SharePoint lists.
• SharePoint Folder — for files stored in a SharePoint document library or folder.

Both connectors are currently supported for Power BI semantic models.

What You Need Before Connecting

Before starting, make sure you have:

• Power BI Desktop installed
• Access to the required SharePoint site
• Permission to access the SharePoint list or files
• The SharePoint site URL
• A Microsoft account with appropriate access to the SharePoint content

The connection method depends on whether your data is stored in a SharePoint list or in files within a SharePoint folder.

Part 1: Connect Power BI to a SharePoint Online List

Use the SharePoint Online List connector when the information you need is stored as a SharePoint list.

For example, a SharePoint list might contain:

• Customer information
• Employee records
• Project details
• Task information
• Sales records
• Issue tracking
• Survey responses

Step 1: Open Power BI Desktop

Open Power BI Desktop.

From the Home tab, select:

Home → Get Data

If necessary, select More to view the complete list of connectors.

Step 2: Select SharePoint Online List

In the Get Data window, search for:

SharePoint Online List

Select the connector and choose:

Connect

Microsoft currently lists SharePoint Online List as a supported connector for Power BI semantic models.

Step 3: Enter the SharePoint Site URL

Power BI asks for the SharePoint site URL.

Enter the URL of the SharePoint site containing the list you want to analyze.

For example:

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

Use the site URL, rather than the URL of an individual list item or document.

Microsoft recommends using the SharePoint site address when connecting to a SharePoint Online list.

Tip: If you are unsure which URL to use, open the SharePoint site in your browser and copy the site address from the address bar.

Step 4: Authenticate Your SharePoint Account

If this is the first time Power BI is connecting to the SharePoint site, you may be prompted to select an authentication method and provide your credentials.

For SharePoint Online, use the appropriate Microsoft account associated with your organization's SharePoint access.

Select the required authentication level and then select:

Connect

The available authentication methods can vary depending on the connector and environment. Microsoft currently documents authentication options for SharePoint Online List that include Microsoft Account and other supported methods depending on the host experience

Step 5: Select the SharePoint List

After authentication, Power BI opens the Navigator.

The Navigator displays the available SharePoint content that your account can access.

Select the required SharePoint list.

For example:

Project Tasks

or

Customer Information

You can then choose:

Load

or

Transform Data

Select Transform Data if you need to clean or modify the information before creating your report.

Microsoft's current connection flow uses the Navigator to select the required SharePoint data and then either load or transform it.

Step 6: Transform SharePoint List Data

Selecting Transform Data opens the Power Query Editor.

Power Query allows you to prepare the SharePoint list before loading it into the Power BI model.

You can:

• Remove unnecessary columns
• Rename columns
• Change data types
• Filter records
• Remove duplicates
• Replace missing values
• Split columns
• Combine information
• Create custom columns

For example, a project task list might contain:

• Project Name
• Task Name
• Assigned To
• Start Date
• Due Date
• Status
• Priority
• Completion Percentage

You can remove fields that are not required and standardize the remaining data before creating the report.

Step 7: Set the Correct Data Types

It is important to assign the correct data type to SharePoint columns.

For example:

• Project Name → Text
• Due Date → Date
• Completion Percentage → Decimal Number
• Task Completed → True/False

Microsoft notes that SharePoint Boolean values can behave inconsistently between Power BI Desktop and Power BI Service if the column data type is not explicitly defined. Setting the correct data type in Power Query helps avoid incorrect filtering or empty visuals.

Step 8: Apply the Changes

After completing the required transformations, select:

Home → Close & Apply

Power BI applies the transformation steps and loads the prepared SharePoint data into the report.

Step 9: Create Your Power BI Report

Once the SharePoint data has been loaded, you can create interactive Power BI visuals.

For example, if your SharePoint list contains project information, you could create:

Project Overview

Display:

• Total Projects
• Active Projects
• Completed Projects
• Delayed Projects

Tasks by Status

Show tasks grouped by:

• Not Started
• In Progress
• Completed
• On Hold

Tasks by Owner

Compare the number of tasks assigned to different team members.

Overdue Tasks

Identify tasks that have passed their due date but are not completed.

Project Progress

Compare project completion percentages.

Related Offerings

Part 2: Connect Power BI to Files in a SharePoint Folder

If your data is stored in Excel, CSV, or other supported files inside a SharePoint document library, use the SharePoint Folder connector.

This is particularly useful when an organization stores multiple files with a consistent structure and wants Power BI to combine them into one dataset.

For example:

• Monthly sales Excel files
• Department reports
• Employee records
• Project files
• Regional performance files

The SharePoint Folder connector supports combining files from a SharePoint folder.

Step 10: Select the SharePoint Folder Connector

In Power BI Desktop, select:

Home → Get Data

Search for:

SharePoint Folder

Select the connector and choose:

Connect

Step 11: Enter the SharePoint Site URL

Enter the URL of the SharePoint site containing the document library or folder.

Use the SharePoint site URL, not the complete URL of an individual file.

For example:

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

Select:

OK

Microsoft's current SharePoint Folder connector documentation uses the SharePoint site URL to establish the connection.

Step 12: Sign In

If prompted, select the appropriate authentication method and sign in with an account that has access to the SharePoint files.

Then select:

Connect

The SharePoint Folder connector supports organizational authentication and other authentication methods depending on the Power Query host.

Step 13: Review the Available Files

After connecting, Power BI displays information about the files available in the SharePoint folder and its subfolders.

You may see columns such as:

• File Name
• Extension
• Date Modified
• Folder Path
• Content

Review the available files and identify the files required for your report.

Step 14: Combine SharePoint Files

If multiple files have the same structure, Power BI can combine them.

Select:

Combine & Transform Data

This opens Power Query and creates a process for combining the files.

Microsoft identifies Combine & Transform Data and Combine & Load as the easiest ways to combine files from a SharePoint folder.

Important: The files being combined should generally have the same structure and format. If different files contain different columns or layouts, the combination process may require additional transformation.

Step 15: Transform the Combined Data

Power Query opens with the combined data.

You can now:

• Remove unnecessary columns
• Change data types
• Filter files
• Rename fields
• Remove duplicates
• Standardize values
• Create calculated columns
• Exclude unwanted files

You can also filter the file list before combining the files if certain files should not be included.

Step 16: Apply the Changes

After preparing the combined data, select:

Home → Close & Apply

The combined data is then loaded into Power BI.

Step 17: Create a Report From SharePoint Files

Once the data has been loaded, create Power BI visualizations based on the information contained in the SharePoint files.

For example, if the folder contains monthly sales files, you could create:

• Monthly Revenue
• Sales by Region
• Sales by Product
• Sales by Salesperson
• Year-over-Year Performance

Common Issues When Connecting Power BI to SharePoint

1. SharePoint Site URL Is Invalid

Make sure you use the SharePoint site URL, not the URL of a specific document or list item.

For example, use:

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

rather than a long URL pointing directly to an Excel file.

Microsoft specifically recommends using the root/site address when establishing the SharePoint connection.

2. SharePoint Data Is Not Visible

If the required list or files do not appear:

• Check your SharePoint permissions.
• Confirm that you are using the correct Microsoft account.
• Verify the site URL.
• Make sure the content still exists.
• Confirm that the list or document library is accessible.

3. Authentication Failed

If authentication fails:

• Check your Microsoft account.
• Confirm that you have permission to access the SharePoint site.
• Re-enter your credentials if necessary.
• Review the authentication method selected in Power BI.

If your organization uses additional security policies such as multifactor authentication or Conditional Access, these policies may also affect access.

 4. SharePoint List Columns Show Incorrect Data

Review the data types in Power Query.

This is particularly important for:

• Boolean values
• Dates
• Date/time fields
• Numeric values

Explicitly assigning the correct data type can prevent inconsistent filtering and visual results.

5. SharePoint Folder Files Are Not Combining Correctly

When using SharePoint Folder, check whether the files have a consistent structure.

For example, if one Excel file contains:

Date | Product | Sales

but another contains:

Date | Product | Sales | Region

the combine process may require additional transformation.

Microsoft recommends using a consistent file format and structure when combining files from a folder.

6. Some Files Should Not Be Included

If a SharePoint folder contains files that should not be included in the report, select Transform Data rather than immediately combining everything.

You can then filter the file list based on:

• File name
• Extension
• Folder path
• Date modified

and combine only the required files.

7. Refresh Is Not Working

If the report works in Power BI Desktop but does not refresh correctly in Power BI Service:

• Check the data source credentials.
• Verify SharePoint permissions.
• Check the semantic model refresh settings.
• Confirm that the SharePoint site and files are still available.
• Review gateway requirements if applicable.

Best Practices for Power BI and SharePoint

Choose the Correct Connector

Use SharePoint Online List when your data is stored in a SharePoint list.

Use SharePoint Folder when your reporting data is stored in files within a SharePoint document library or folder.

Choosing the appropriate connector makes the data preparation process easier.

Keep SharePoint Data Structured

For SharePoint lists, use clear column names and consistent values.

For SharePoint files, maintain a consistent file structure when multiple files will be combined.

Use Power Query for Data Preparation

Use Power Query to clean and standardize SharePoint data before building the report.

This helps maintain a reliable dataset and reduces unnecessary manual work.

Load Only the Data You Need

Avoid importing unnecessary columns and files.

For SharePoint Folder connections, filter out files that are not required before combining the remaining files.

Use Clear Data Types

Always review the data types of important fields such as:

• Dates
• Numbers
• Currency
• Boolean fields
• Text

Correct data types are particularly important when creating filters, calculations, and visualizations.

Plan Your Refresh Strategy

SharePoint content can change frequently as users add, modify, or replace files and list records.

Configure the Power BI refresh schedule according to how frequently the underlying SharePoint data changes and how current the report needs to be.

Conclusion

Connecting SharePoint to Power BI provides an effective way to turn SharePoint-based business information into interactive reports and dashboards.

Power BI supports different SharePoint connection methods depending on where the information is stored. SharePoint Online List is suitable for structured information stored in SharePoint lists, while SharePoint Folder is useful for analyzing and combining files stored in SharePoint document libraries. Both are supported in Power BI semantic models.

By selecting the appropriate connector, preparing the data with Power Query, creating a structured data model, and configuring refresh settings, organizations can turn everyday SharePoint information into useful business insights.