Power BI is a business intelligence platform that equips individuals and businesses to generate insights by connecting to their data and building interactive reports and data visualization.
Excel files (xlsx) are a common type of data source file that Power BI can connect to for building these reports, and these files can be stored locally on a computer. Connecting to a local xlsx file is simple, however, there is another – more collaborative – way to connect to these files through SharePoint folders.
Using SharePoint folders connects you and your team to shared content and resources in the cloud, allowing for a more streamlined, consistent, and accurate connection process.Â
This blog post will cover:
- How to collaborate with Excel files to produce a more self-sustaining data model
- How to connect to multiple xlsx data files through SharePoint folders
- How to incorporate new data into the data connection
Best Case Scenario: Survey Reports
Although this process is of connecting to Excel files via SharePoint is ideal in many scenarios, it is unfortunately not a one-size-fits-all solution for every situation. One scenario that would benefit from this type of data connection is survey reports where new data is produced monthly.
In this scenario, each month a new data file is created and must be added to a report to show the recent survey responses and changes in response. While this is just one example, I’m sure you can easily think of many other cases in which this solution would be viable.
The big takeaway is that if there is data from multiple files that can or need to be appended into one table, including new data files that incrementally need to be added to the table, then this data source hack might be the perfect solution.
Requirements
Before I get into the implementation steps, it is important to detail the few requirements for automating Excel reporting in this way. If any of these requirements are not fulfilled, the steps below will produce errors.
- Data must be stored on a SharePoint site within a folder in the Documents library
- The data files must be xlsx. If not, converting files to xlsx will need to be an additional step
- The sheet names must match across all files that will be combined, including new files that will be added
- The column names and data must match the columns in the other tables. The data must essentially be fit to be appended.
- There is a way to work around this if some files have unique columns, but that’s for another blog!
How to Connect to the Data from Power BI Desktop
- From Power BI Desktop click Get Data and select More…
- Select SharePoint Folder and click Connect
- Copy and paste the root URL for the SharePoint site and click OK. The root URL is the URL of the Home page of the SharePoint site, it ends in the team name as shown below.
- Example: https://<tenant>.sharepoint.com/sites/<teamname>
- If you haven’t already, authenticate your connection to the SharePoint site by signing into your organizational account that is linked to the site.
- All the files that exist in the Documents library will appear as separate rows in a popup window. This is fine because we will filter the data next. From here, click Transform Data to open up Power Query.
- Filter the Folder Path column to the folder that contains the data. There will be a unique Folder Path value for every folder within the Documents library of the SharePoint site.
- Once the files are filtered appropriately, click the Combine Files button on the Content column header.
- A new popup window will appear, here select a sample file and sheet. This file will be used as a template to determine which columns to include in the combined data table. The default sample file is the first file, but it can be changed to any other file imported to Power BI. Once the sample file and sheet are selected, the data will appear in the right pane. Click OK, and Power Query will do the heavy lifting to combine the files.
- The combined table that will be used will appear in the queries panel as Query1, Feel free to rename the table. This table will contain all the appended data.
- Make any other necessary transformations and click Close and Apply.
Video Run-Through of Connecting to the Data
In the video below, I go through step by step in Power BI Desktop to connect to the Excel files stored in a SharePoint folder and use Power Query to consolidate the files into one appended table.
I use an example of connecting to Superstore data for sales orders occurring in three different months. The xlsx files are stored in the Superstore Data folder within a SharePoint site, and all matching sheet names are utilizing the default name Sheet1. In this example, combining these monthly sales files allow for the creation of a report that shows change over time.
How to Add New Data
One of the biggest benefits of connecting to data in this way is the quick and easy way to add new data to the model. Let’s go over the quick steps to add new data files to the model.Â
Step 1: Prep Data
Whenever there is a new data file that needs to be added to the data model, it must first be altered to fit the requirements described above. This means that the file is xlsx, the sheet name matches the others, and the structure of the data also matches. In a perfect world, the data is already in the correct format, but in the case that it is not, these quick steps should be taken.
Step 2: Upload Data
Once the data fits the requirements, the file can be uploaded to the SharePoint site under the Documents tab. Upload the file to the folder that is filtered in step 5 of Connecting to Data.
Step 3: Refresh Data
The last step is to refresh the data, which can be done in Power BI Service. If the report is not published to Power BI Service, it can be refreshed easily in Power BI Desktop as well.
And voila!, the data table and the report will update with the new data. This solution will make it easy to accommodate new data additions.
Tips
Be intentional with the xlsx file naming conventions.Â
- The names will be included in the combined table as Source.Name, and could be useful to include in the report. For example, If the xlsx files do not contain a column for dates, consider adding the dates to the file names. Once imported, the file names can be transformed to create a column of dates.
Utilize folders in the Documents library of the SharePoint site.Â
- When connecting to the data, all files that exist in the Documents library will be imported initially. Folders within this library can be used via their folder path to filter out which data files you would like to combine. Separating the data based on folders can be a simple way to ensure you’re connecting to the right files.Â
Closing
Utilizing SharePoint folders is a great solution for combining multiple data files into one table in order to consolidate and automate your Power BI reporting. It also enables easy data additions for smooth data refreshes and is a great way to connect to data collaboratively. If your situation calls for it, give it a try. You won’t be disappointed.Â
If you’re looking for additional help or are interested in learning how to better automate Power BI, reach out to the experts at phData for help, guidance, and best practices.
