SharePoint Integration with Power BI: Complete Guide

Data management and sharing are essential for businesses where teams need to collaborate, access information, and make informed decisions. But storing business data is only one part of the process. To make that data useful, businesses also need a way to analyse it and turn it into meaningful insights.

This is where SharePoint integration with Power BI can help.

Microsoft SharePoint enables organisations to store, organise, manage, and share business information, while Microsoft Power BI transforms data into interactive reports and visualisations. By connecting the two, businesses can analyse data stored in SharePoint without repeatedly moving information between different systems.

The integration can be useful for organisations that maintain project data, operational records, financial information, employee information, departmental reports, or other business data within SharePoint.

In this guide, we will explain how to connect Power BI to SharePoint, use SharePoint Lists and folders as data sources, embed Power BI reports in SharePoint Online, and address some common integration issues.

What is Microsoft SharePoint?

Microsoft SharePoint is a web-based collaboration and content management platform from Microsoft. It helps organisations create sites, manage documents, share information, and enable teams to collaborate from a central location.

Businesses commonly use SharePoint to manage:

• Documents and files

• SharePoint Lists

• Internal sites and intranets

• Policies and procedures

• Departmental information

• Project documentation

• Business records

• Team collaboration

• Internal communications

As part of the Microsoft 365 ecosystem, SharePoint also works with applications such as Teams, OneDrive, Power Automate, Power Apps, and Power BI.

Because business information can be stored centrally, employees can access the information they are authorised to use without relying heavily on email attachments or disconnected file storage.

What is Microsoft Power BI?

Microsoft Power BI is Microsoft's business intelligence and data visualisation platform. It enables organisations to connect to different data sources, transform information, build data models, and create interactive reports and dashboards.

Power BI can connect to a wide range of business data sources, including Excel, SQL databases, cloud applications, SharePoint, Dynamics 365, and many other systems.

Businesses can use Power BI to analyse areas such as:

• Sales performance

• Financial performance

• Inventory

• Projects

• Operations

• Customer service

• Employee performance

• Procurement

• Marketing

• Business KPIs

Instead of working through large spreadsheets or multiple reports manually, decision-makers can use visual dashboards to identify patterns, exceptions, and trends more easily.

Power BI Desktop can be used to create reports and data models, while the Power BI service provides cloud-based capabilities for publishing, sharing, collaboration, and report consumption.

What is SharePoint Integration with Power BI?

SharePoint integration with Power BI connects data stored in SharePoint with Power BI so that it can be transformed, analysed, and presented through interactive reports.

For example, suppose your organisation maintains project information in a SharePoint List.

Instead of exporting the list to Excel every week and manually creating charts, you can connect Power BI directly to the SharePoint data source. Power BI can then use that data to create reports showing project status, costs, deadlines, resource utilisation, or other relevant metrics.

There are several ways Power BI and SharePoint can work together.

You can:

• Connect Power BI to a SharePoint Online List

• Connect Power BI to files stored in SharePoint folders

• Analyse Excel or other supported files stored in SharePoint

• Publish Power BI reports to the Power BI service

• Embed interactive Power BI reports within SharePoint Online pages

The appropriate approach depends on where your data is stored and how users need to consume it.

Why Integrate SharePoint with Power BI?

SharePoint is highly effective for managing content and collaborative information, while Power BI specialises in data analysis and visual reporting.

Connecting the two can help businesses get more value from the information already stored in SharePoint.

Reduce Manual Data Handling

Without integration, employees may repeatedly export SharePoint information into spreadsheets before analysing it.

Connecting Power BI to SharePoint reduces the need for this repetitive process and helps create a more consistent reporting workflow.

Build Interactive Reports

SharePoint data can be transformed into charts, tables, KPIs, graphs, and other interactive visualisations within Power BI.

This makes large datasets easier for users to understand.

Improve Decision-Making

Managers can use Power BI reports to monitor business information stored in SharePoint and identify trends or exceptions that may otherwise be difficult to spot.

Centralise Reporting

Businesses can make Power BI reports available through SharePoint Online pages, giving employees a familiar place to access both business content and analytics.

Improve Collaboration

Teams can work with documents and operational information in SharePoint while using Power BI to analyse relevant data.

This provides a more connected experience across Microsoft business applications.

Different Ways to Connect Power BI with SharePoint

Before setting up the integration, it is important to understand that there isn't only one way to connect Power BI and SharePoint.

The connection method depends primarily on the type and location of your data.

1. SharePoint Online List Connector

Use this method when your structured business information is maintained in a SharePoint List.

For example:

• Project tracking

• Employee records

• Issue tracking

• Tasks

• Asset information

• Operational records

Power BI can connect to the SharePoint Online List and use its columns and records for analysis.

2. SharePoint Folder Connector

If your data is stored in files within a SharePoint document library or folder, you can use the SharePoint Folder connector.

This can be particularly useful when multiple files with a similar structure need to be combined for reporting.

For example, a business may maintain monthly Excel reports in a SharePoint folder. Power BI can connect to the folder and combine relevant files into a reporting dataset.

3. Embed Power BI Reports in SharePoint Online

The integration can also work in the opposite direction.

Once you have created and published a Power BI report, you can embed it within a modern SharePoint Online page using the Power BI web part.

This allows employees to interact with reports without having to navigate separately to the Power BI service each time.

How to Connect Power BI to a SharePoint Online List

If your information is stored in a SharePoint List, Power BI Desktop provides a dedicated SharePoint Online List connector.

Here is the current process.

Step 1: Open Power BI Desktop

Launch Power BI Desktop and sign in with the appropriate Microsoft organisational account where required.

Open an existing report or create a new Power BI report.

Step 2: Select Get Data

From the Home tab in Power BI Desktop, select Get Data.

Search for or select:

SharePoint Online List

Select the connector and continue.

Step 3: Enter the SharePoint Site URL

Power BI will ask you to provide the SharePoint site URL.

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

For example:

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

Enter the appropriate site URL and select OK.

Depending on your Power BI version and connector options, you may also see different connector implementation options.

Step 4: Authenticate Your Account

If you are connecting to the SharePoint site for the first time, Power BI will ask you to authenticate.

For SharePoint Online in a business environment, this will normally involve signing in with your organisational Microsoft account.

Make sure the account has permission to access the SharePoint data you want to analyse.

After authentication, select Connect.

Step 5: Select Your SharePoint List

Power BI will display the available data through the Navigator.

Select the SharePoint List you want to use.

You can then choose:

Load – to load the data directly into Power BI.

or

Transform Data – to open Power Query and clean or modify the data before loading it.

For most business reporting scenarios, reviewing and transforming the data before creating a report can help ensure that the model contains only the information you actually need.

Step 6: Prepare the Data

Within Power Query, you can perform transformations such as:

• Remove unnecessary columns

• Rename columns

• Change data types

• Filter records

• Replace values

• Merge queries

• Create calculated fields

• Clean inconsistent data

Once your data is ready, apply the changes and load it into Power BI.

Step 7: Create Your Power BI Report

After loading the SharePoint data, you can start creating your report.

Depending on your requirements, you can add:

• Charts

• Tables

• KPI cards

• Slicers

• Filters

• Trend visualisations

• Performance indicators

You can then publish the report to the Power BI service if it needs to be shared with other users.

How to Connect Power BI to a SharePoint Folder

Sometimes the information you need isn't stored in a SharePoint List. Instead, it may exist within Excel, CSV, or other supported files stored in a SharePoint document library.

In this situation, the SharePoint Folder connector can be useful.

Step 1: Select Get Data

Open Power BI Desktop and select:

Home > Get Data

Search for:

SharePoint Folder

Select the connector.

Step 2: Enter Your SharePoint Site URL

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

Again, use the SharePoint site URL, rather than pasting a direct URL to an individual document.

Step 3: Authenticate

Sign in using the appropriate organisational account and establish the connection.

Step 4: Find the Required Files

Power BI will display files available through the SharePoint location.

You can use Power Query to filter the results based on:

• Folder path

• File name

• File extension

• Date

• Other available attributes

Step 5: Combine and Transform Data

If several files follow the same structure, Power BI can combine them into a single dataset.

For example, if each regional team uploads a monthly sales spreadsheet to SharePoint using the same format, Power BI can potentially consolidate those files for reporting.

This can significantly reduce the amount of manual consolidation required.

How to Embed a Power BI Report in SharePoint Online

Connecting SharePoint data to Power BI solves the data analysis side of the integration.

But organisations can go further by displaying Power BI reports directly within SharePoint Online.

Microsoft provides a Power BI web part for modern SharePoint Online pages.

Step 1: Publish Your Report

Create your report in Power BI and publish it to the appropriate workspace in the Power BI service.

Step 2: Get the SharePoint Report URL

Open the report in the Power BI service.

Go to:

File > Embed report > SharePoint Online

Copy the report URL provided by Power BI.

Step 3: Edit Your SharePoint Page

Open the SharePoint Online page where you want the report to appear and select Edit.

The Power BI web part works with modern SharePoint Online pages.

Step 4: Add the Power BI Web Part

Select the + option to add a web part.

Choose Power BI from the available web parts.

Select Add report and paste the report URL copied from the Power BI service.

The report should then load within the SharePoint page.

Step 5: Configure the Report

Depending on your requirements, you can configure options such as:

• Report page

• Display size

• Navigation pane

• Filter pane

Once everything is configured correctly, publish the SharePoint page.

Users with the appropriate permissions and licensing can then interact with the Power BI report directly from SharePoint.

Power BI and SharePoint Permissions

One important point businesses sometimes overlook is that embedding a Power BI report in SharePoint does not automatically give users access to the report.

SharePoint permissions and Power BI permissions need to be managed appropriately.

A user may have access to the SharePoint page but still be unable to view the embedded Power BI report if they don't have permission to the report itself.

Similarly, Power BI security features such as Row-Level Security (RLS) continue to apply to embedded reports.

This is useful when different employees need to see different portions of the same business dataset.

For example, a regional manager may only be allowed to see data relating to their region.

Power BI Licensing for SharePoint Integration

Licensing requirements depend on how Power BI content is created, published, shared, and consumed.

For embedding a report in SharePoint Online, report creators generally require an appropriate Power BI licence, such as Power BI Pro or Premium Per User (PPU).

Viewer requirements depend on the licensing and capacity model being used by the organisation.

For example, users may require Power BI Pro or PPU licences, or the content may need to be hosted on an appropriate Microsoft Fabric/Power BI capacity that supports the required consumption scenario.

Because Microsoft licensing and capacity options can change, businesses should verify their exact licensing requirements before rolling out Power BI reports to a large number of SharePoint users.

How Does Data Refresh Work Between SharePoint and Power BI?

Another important consideration is data refresh.

When SharePoint is used as a Power BI data source, businesses normally don't want to rebuild the report every time information changes.

After publishing a report, you can configure refresh settings within the Power BI service according to the data source, authentication method, licensing, capacity, and reporting requirements.

For example, if employees regularly update an operational SharePoint List, the Power BI semantic model can be refreshed so that reports reflect newer information.

The appropriate refresh configuration will depend on your architecture.

You should therefore review:

• Data source credentials

• Refresh frequency

• Dataset/semantic model size

• Power BI capacity

• SharePoint permissions

• Business reporting requirements

For business-critical reporting, it is also useful to monitor refresh failures so that decision-makers don't unknowingly work with outdated information.

Common Power BI and SharePoint Integration Issues

Although connecting Power BI with SharePoint is relatively straightforward, businesses can encounter problems depending on their SharePoint structure, permissions, data volume, and Power BI configuration.

Here are some common issues.

Incorrect SharePoint URL

One of the most common mistakes is using the URL of an individual file, folder, or SharePoint page when the connector expects the SharePoint site address.

Always check which URL format is required by the connector you are using.

Authentication Errors

Power BI needs an account with appropriate access to the SharePoint site.

Authentication can fail if:

• The wrong account is being used

• Credentials have changed

• Permissions have been removed

• Organisational security policies affect the connection

Missing Columns

If expected SharePoint List columns aren't appearing correctly, review the connector configuration and SharePoint List view.

The SharePoint Online List connector provides different ways of retrieving list information, and the columns available can depend on the selected view and configuration.

Too Much Unnecessary Data

Importing every file, column, or record from SharePoint can make the Power BI model unnecessarily complex.

Filter your SharePoint data and remove unnecessary columns as early as practical during the Power Query process.

Embedded Report Doesn't Appear

If users can access a SharePoint page but cannot see the embedded report, check their Power BI report permissions and licensing.

Adding the Power BI web part to SharePoint does not automatically grant access to the underlying report.

Refresh Failure

A report may work correctly initially but later stop receiving new data because of credential or refresh configuration issues.

Check refresh history and data source credentials in the Power BI service when troubleshooting stale reports.

Best Practices for Power BI and SharePoint Integration

A technically working integration is not always the same as an efficient reporting solution.

Following a few practices can make the setup easier to manage.

Keep SharePoint Data Structured

Power BI works more effectively when the underlying information follows a consistent structure.

Use clear column names, consistent data types, and well-designed SharePoint Lists.

Avoid Loading Unnecessary Data

Import only the information required for reporting.

Large amounts of unnecessary data can increase model complexity and make maintenance more difficult.

Plan Permissions Early

Determine who should be able to access:

• SharePoint data

• Power BI workspaces

• Reports

• Embedded SharePoint reports

This is particularly important where financial, customer, HR, or other sensitive business information is involved.

Use Power Query for Data Preparation

Instead of manually cleaning source files repeatedly, use Power Query transformations to establish a repeatable data preparation process.

Design Reports for Their Audience

Executives may need high-level KPIs, while operational teams may require detailed information.

Design the Power BI report around the decisions users actually need to make.

Monitor Refreshes

A visually impressive dashboard isn't useful if the underlying information is outdated.

Monitor refresh history and resolve failures promptly.

Business Use Cases for SharePoint and Power BI Integration

The combination can be used across different departments and industries.

Project Management

Store project records in SharePoint and use Power BI to analyse:

• Project status

• Deadlines

• Budget utilisation

• Outstanding tasks

• Resource allocation

Sales Reporting

If sales-related information is maintained in SharePoint, Power BI can provide visual reports around:

• Sales performance

• Opportunities

• Regional performance

• Product performance

• Targets versus actuals

HR Reporting

SharePoint Lists can store appropriate employee or administrative information, while Power BI can help authorised users analyse workforce metrics.

Operational Reporting

Businesses can use the integration to monitor:

• Tasks

• Incidents

• Assets

• Service requests

• Compliance information

• Departmental KPIs

Financial Reporting

Where appropriate financial data or supporting files are maintained in SharePoint, Power BI can consolidate information and provide management-level reporting.

When Should You Consider Professional SharePoint and Power BI Integration?

A basic SharePoint and Power BI connection can often be configured internally.

However, integration becomes more complicated when organisations have multiple sites, large datasets, complex permissions, multiple business systems, or advanced reporting requirements.

Professional support may be useful when you need to:

• Combine information from multiple SharePoint sites

• Connect SharePoint data with Dynamics 365 or other business systems

• Build executive Power BI dashboards

• Configure complex data models

• Implement Row-Level Security

• Improve slow Power BI reports

• Automate reporting processes

• Resolve recurring refresh problems

• Establish scalable reporting architecture

• Manage complex Power BI permissions

This is particularly important when Power BI reports are being used for operational or strategic decision-making across multiple departments.

Conclusion

SharePoint and Power BI solve different business requirements, but they can become much more valuable when used together.

SharePoint provides a central environment for managing and collaborating on business information, while Power BI enables organisations to transform that information into interactive reports and actionable insights.

By integrating Power BI with SharePoint Online, businesses can reduce manual reporting, analyse SharePoint Lists and files, centralise business intelligence, and even provide interactive reports directly within SharePoint pages.

However, successful integration requires more than simply connecting the two applications. Data structure, security, permissions, refresh configuration, report design, and licensing all need to be considered.

If your organisation needs help connecting SharePoint with Power BI, building interactive dashboards, configuring data models, or integrating Microsoft business applications, Dynamics Square UK can help.

As a trusted Microsoft Solutions Partner, Dynamics Square UK helps businesses implement and optimise Microsoft technologies according to their reporting and operational requirements.

Contact Dynamics Square UK to discuss your SharePoint and Power BI integration requirements with our Microsoft consultants.

Some FAQS

1. Can Power BI connect directly to SharePoint?

Yes. Power BI provides connectors that can be used to access data stored in SharePoint, including the SharePoint Online List and SharePoint Folder connectors.

2. How do I connect a SharePoint List to Power BI?

In Power BI Desktop, select Get Data, choose SharePoint Online List, enter the SharePoint site URL, authenticate your account, and select the required list from the Navigator. You can then load the data or transform it using Power Query.

3. Can Power BI read Excel files stored in SharePoint?

Yes. Power BI can work with supported files stored in SharePoint. The SharePoint Folder connector is particularly useful when you need to access or combine files stored within a SharePoint document library.

4. Can I embed a Power BI dashboard in SharePoint?

Power BI reports can be embedded in modern SharePoint Online pages using Microsoft's Power BI web part. Users still need the appropriate report permissions and licensing to view the content.

5. Does embedding a Power BI report give SharePoint users access automatically?

No. Embedding does not automatically grant permission to the Power BI report. Users must have the appropriate Power BI access in addition to access to the SharePoint page.

6. Can SharePoint data refresh automatically in Power BI?

Power BI supports scheduled refresh for supported data sources and configurations. The exact refresh options depend on the data source, credentials, Power BI licence or capacity, and the architecture being used.

7. Why is my SharePoint List not showing all columns in Power BI?

This can happen because of the selected SharePoint view, connector implementation, lookup columns, or list configuration. Check the connector settings and the SharePoint List view before rebuilding the report.

8. What URL should I use when connecting Power BI to SharePoint?

In most SharePoint connector scenarios, you should provide the SharePoint site URL rather than a direct URL to an individual document or subfolder. Always check the requirements of the specific Power BI connector you are using.

9. Is SharePoint and Power BI integration secure?

It can be configured securely using Microsoft authentication, SharePoint permissions, Power BI permissions, and security features such as Row-Level Security. The security configuration should be planned according to the sensitivity of the underlying business data.

10. Do I need Power BI Pro to embed reports in SharePoint?

The person embedding a Power BI report in SharePoint Online generally needs Power BI Pro or Premium Per User. Viewer licensing depends on the organisation's Power BI/Fabric capacity and licensing configuration.