Power BI stands out for its seamless integration with Microsoft products, including SharePoint. But how do you connect Power BI to SharePoint? Can you access all the files and extract data from a SharePoint location?
As a leading Power BI consulting service, we created this tutorial to help you integrate Power BI with SharePoint. We’ll use Power BI Desktop and SharePoint connectors in this guide. Let’s begin!
Can Power BI Be Linked to SharePoint?
Yes, SharePoint and Power BI can be linked.
If your organization uses SharePoint, either online or on-premises, you can connect to SharePoint from Power BI to create visualizations.
How Do I Connect Power BI to a SharePoint Location?
A SharePoint location is simply a folder or list within SharePoint.
To connect Power BI to a specific SharePoint location, you need to use the right connector, depending on what you want to access. Plus, you need to provide your SharePoint site URL.
Connectors
Here are the 3 Power BI built-in connectors for SharePoint:
SharePoint Folder Connector
This connector helps you link to a SharePoint folder. It’s particularly useful when you want to combine multiple files with the same format and structure into one table. For example, you can use this connector to connect Power BI to SharePoint Excel files in a subfolder and combine them into a single dataset.
With this connector, you can connect Power BI online to SharePoint files, online and on-premises.
SharePoint List Connector
A list in SharePoint is essentially a web-based spreadsheet, providing a flexible and customizable way to store and manage data.
This connector lets you import SharePoint list data into Power BI for analysis and reporting. It can connect to a SharePoint list from either SharePoint Online or SharePoint on-premises.
SharePoint Online List Connector
This connector is a specific version of the SharePoint list connector designed to work only with SharePoint Online. It has two versions: v1.0 and v2.0.
The v2.0 version has improved APIs and greater usability, allowing you to either retrieve all columns or default view columns of a SharePoint list. The ‘All view’ includes all user-created and system-defined columns. On the other hand, the ‘Default view’ is what you’ll see when looking at the list online.
Site URL
When connecting with a SharePoint site, Power BI will ask you to input the site URL. The ‘site URL’ here refers to the root URL of your SharePoint site, exclusive of any subfolders.
Here’s an example: https://yourcompany.sharepoint.com/sites/yoursitename.
If you accidentally enter an incorrect site URL, a yellow warning icon will appear next to the URL text box.
How Do I Get a List of All Files in a SharePoint Folder?
To get a list of all files in a SharePoint folder using Power BI, you can use the SharePoint folder connector. Once connected, you’ll see a list of all files inside your SharePoint folder, including any files within any subfolders. You can then load the files into Power BI for further analysis.
For a detailed step-by-step on how to link Power BI to a SharePoint folder, just keep reading below.
How Do I Connect Power BI to a SharePoint Folder?
To connect Power BI to a SharePoint folder, follow these steps:
- In the Home tab, click Get Data and select More
- Select the SharePoint folder connector. You can also use the search text box to find it quickly.
- Enter your SharePoint Site URL, then click OK to continue.
- If this is the first time you’ve visited this site address, select the appropriate authentication method (Anonymous, Windows, or Microsoft Account). Enter your credentials and choose which level to apply these settings to. Then select Connect.
- In the data preview window, see that info about all of the files in the SharePoint folder is displayed. In addition, info about any files in any subfolders is also displayed.
- Select Combine & Transform Data to combine the data in the files of the selected SharePoint folder and load the data into the Power Query Editor for editing. Or select Combine & Load to load the data from all of the files in the SharePoint folder directly into your app.
Unable to connect to a SharePoint folder in Power BI? The first thing to ensure is that you’re connecting with the correct SharePoint site URL and credentials.
How Do I Connect a SharePoint Sub-Folder To Power BI?
To connect Power BI to a SharePoint subfolder, the method is similar to connecting a main folder using a SharePoint folder connector. Once you have connected, click on Transform Data. In the Power Query Editor, you’ll see a list of all files across all folders. Here, filter the Folder Path column to include only the path of your specific subfolder. This way, you connect to the SharePoint subfolder without involving the root directory.
How Do I Get Data From a SharePoint List?
To get data from a SharePoint list, follow these steps:
- In the Home tab, click Get Data and select More
- Select the SharePoint list connector. You can also use the search text box to find it quickly.
- Enter your SharePoint Site URL, then click OK to continue.
- If this is the first time you’ve visited this site address, select the appropriate authentication method (Anonymous, Windows, or Microsoft Account). Enter your credentials and choose which level to apply these settings to. Then select Connect.
- In the Navigator window, select the list you want to import, then either transform the data in the Power Query editor by selecting Transform Data or load the data by selecting Load.
Note: If you are connecting to a SharePoint Online list, you can also use the SharePoint Online list connector.
Can You Connect a Power BI Dashboard to SharePoint?
Yes. You can connect a Power BI dashboard to SharePoint using the Power BI web part. This feature allows you to embed Power BI reports directly into your SharePoint pages, making data analysis accessible within SharePoint.
To do this:
- Publish your Power BI report to the Power BI Service.
- In SharePoint, edit the Page where you want to add the report.
- Select the + in New dropdown menu.
- In the Data Analysis section, select Power BI web part.
- Click the Add report. In the Power BI web part properties, paste the link of your Power BI report.
Once added, the Power BI report loads and functions interactively, just like in the Power BI Service.
P3 Adaptive Is Here To Help!
Need a hand linking Power BI to your SharePoint folder? Or perhaps you’re not quite sure how to make the most of your SharePoint data on your Power BI dashboards? No sweat. We’re always ready to help.
We’re P3 Adaptive, a top-notch Power BI consulting service with tons of experience weaving together Power BI and SharePoint. Our team will walk you through the process step by step. We’ll ensure you’re getting the most out of your SharePoint data.
Reach out to us today and uncover game-changing insights for your business. Let’s turn your data into action—together!
Your email address will not be published.