Connecting Power BI with SharePoint folders gives users the ability to import individual or multiple Excel, CSV or other file formats into Power BI. Being cloud based, connecting to files saved on SharePoint eliminates the need for a Power BI gateway as the data is already on the cloud.
Power BI and SharePoint hosted files also have a number of benefits compared to more traditional network drives, such as improved data governance, easier sharing, and when you publish your Power BI report to PowerBI.com the data is already stored in the Microsoft Cloud.
Let’s jump in!
Importing Excel Files from SharePoint to Power BI
There are two steps to importing Excel files from SharePoint into Power BI. The first is establishing the connection to SharePoint from Power BI Desktop, and then during the import process you will need to select which files to import by filtering a list of all available files on the SharePoint site to only the ones that you need to import and work with.
Connecting Power BI to SharePoint Folders
Connecting Power BI to SharePoint folders is the first step to importing files into Power BI. This process allows users to access and analyze SharePoint-stored data directly in Power BI. For this to work, you will need to have Power BI Desktop installed and access to the SharePoint site.
To start, click on Get Data from the Power BI Ribbon, select More, and either search for SharePoint Folder or select it from the File section.
Searching for connector names is usually faster than figuring out which section a connection is available under in Power BI.
After selecting SharePoint Folder, you’ll be prompted to enter in the Site URL. This is the first part of the URL of your specific site. In our example below, we are connecting to the primary site, but you might need to include a /pages/sitename.
The import part to this is using the base part of the URL. It’s not super intuitive, but we want to connect to a folder in SharePoint then select the files we want to import separately. This step will give us the site contents that we will imit down in the next step.
Importing Individual Files from SharePoint into Power BI
Start by selecting SharePoint folder as your data source and then filtering for the specific file or files you need. After connecting to a SharePoint folder, Power BI will display a list of files that can be imported. Click on Transform data to filter the list of files to only the ones that you are interested in.
When importing a folder full of similar Excel files, this step probably makes more sense, but when you want to import an individual file you’ll need to select “Transform Data” to launch the Power Query editor to filter the list down to the single file you want to import.
The Load button will load a list of files into Power BI, clicking on Transform Data launches the Power Query Editor to limit the list of files.
In our example, we’ll import Cookie Sales.xlsx by clicking the filter button at the top of the column and selecting the file name.
You may have to get more creative when working with large lists of files that are not specifically unique. Typically there’s a way to import just the file you want, but be careful when setting up filters as the number of files in a folder can change over time. The more dynamic you can make a filter the better.
Click OK to limit the list down to a single file, then expand the contents of the file, by clicking the two down arrows in the Content Column.
This is the step where you go from list of files, to the content of an Excel file.
Select the sheet you want to import from the Excel file and click OK.
The data from the Excel file will be loaded into Power Query.
As you can see, importing Excel files from SharePoint in Power BI is easy to do, but not exactly straightforward. Be careful when filtering for file names. If names change or filtering criteria picks up more than the needed file it can cause data refresh errors.
Power BI Refresh Schedules with SharePoint Files
Setting up Power BI refresh schedules ensures that your reports and dashboards reflect the latest data from SharePoint files. Power BI offers the capability to schedule automatic data refreshes, which can be configured to run at specific intervals, keeping your data up to date without manual intervention.
To set up a refresh schedule, first publish your finished Power BI report to the Power BI Service at PowerBI.com. Then navigate to the semantic model (dataset) settings in the Power BI service and specify the frequency and time for the refreshes.
When importing files from SharePoint, you cannot use Direct Query mode, meaning that refreshes must occur on a schedule. Some data sources like SQL allow you to query the data with a live connection meaning that it’s always up to date, but there are some limitations with SharePoint as a data source.
For files that are not updated frequently, you can use Power Automate to trigger a Power BI refresh whenever an Excel file changes, you can learn more here: Dynamically Refresh Power BI Datasets with Power Automate
Power BI Refresh Licensing Requirements
To publish a Power BI report to PowerBI.com a license is required, even though creating a report in Power BI Desktop is free. The most common license is Power BI Pro. Power BI Pro users can schedule refreshes up to eight times a day, the next tier up is Power BI Premium which offers up to 48 refreshes per day.
The choice of licensing can significantly impact your organization’s ability to maintain real-time data analytics when working with Power BI Import mode.
Power BI and SharePoint Tips and Tricks
The following section are some tips and tricks when working with SharePoint files and Power BI to help avoid some common pitfalls that tend to come up.
Some general tips for working with SharePoint hosted files in Power BI:
- Check SharePoint Access to ensure you at least have folder and file read permissions
- Utilize Power BI’s query parameters to filter and transform SharePoint data during import.
- Explore Power BI’s advanced data refresh options, such as incremental refresh, to optimize performance.
- Stay informed about Power BI and SharePoint updates to take advantage of new features and improvements.
The following video from Guy in a Cube does a great job explaining some of the common problems that people will run into after publishing their report from Power BI Desktop to the Power BI Service and setting up scheduled refreshes that’s worth a watch.
SharePoint is one of the most common ways to share files across a team or organization, but there also a couple of additional options that you may have which are worth being aware of.
Power BI Gateways to Access on Premises Files
Power BI Gateways act as a bridge between Power BI service and on-premises data sources. They typically require some coordination to setup between your Power BI Admin and the IT department, but can be a big time saver if you have a number of files saved on shared network drives and don’t have all of your needed data in a SharePoint Folder.
Gateways enable you to schedule refreshes from data that’s not on a cloud service and let you drop files directly into a folder that Power BI will later pickup. You can learn more about Power BI Gateways here: A Practical Guide to Power BI Gateways
Microsoft Fabric and OneLake
Microsoft recently announced a suite of products aimed at bringing Power BI’s data infrastructure into the future. A suite of products help people manage files within a storage account that can quickly be ingested by Power BI and eliminates the need to store files in SharePoint.
Some of the features are still a work in progress, but at least worth being aware of. In 2023, Microsoft announced Microsoft Fabric. It combines features for data engineering and data ingestion that were previously only available in the Azure Portal into Power BI style workspaces, making it easier for business users to manage data in a single place.
The second tool to be aware of is OneLake. This is Microsoft’s attempt at giving business users an interface similar to OneDrive for Windows Desktop but when files are dragged and dropped into folders, they are written to a data lake which is optimized for importing data into Power BI.
Import Excel files hosted on SharePoint starting in Power BI Desktop. Connect Power BI to a SharePoint folder which provides a list of files hosted on the site, then filter the list of files down to the one or ones that you want to import.
Be careful when selecting the import criteria, as the number of files and file names tend to change over time. The more precise and dynamical you can make your filtering the less likely it is to fail in the future.
Files hosted on SharePoint won’t automatically update in Power BI and require a refresh schedule. You’ll need at least a Power BI Pro license to schedule a refresh, and there are ways to set this up to be more dynamic using Power Automate.