Connect to Multiple Data Source Files

This article provides step-by-step instructions on how to add data files in Power BI.

Instructions

Data sources used in Power BI should always be stored in the UMS Power BI HUB library, not on a user’s local machine.  When connecting to data in Power BI, DO NOT use either the Get Data > Excel or Get data > Folder methods. This will create a connection to the local versions instead, and refreshes will fail once the report is published to the service or if a colleague tries to refresh the data in the .pbix file.

Please use the methods described below for connecting multiple homogeneous files from your SharePoint Site in Power BI. For example, if you have weekly data export from PeopleSoft Query and are adding the data to Power BI, this option is ideal. All files must be homogeneous in format and structure. The name of the tab on each Excel file should be the same as well. If you have files whose structures are different, please go to the Connect to Data Source file page.

Get Data

1. In Power BI Desktop, select Get Data > More > SharePoint folder and click ‘Connect’.

   

2. Enter this URL: https://umainesystem.sharepoint.com/sites/UMSPowerBIUsersCommunity_O365 (The URL has to be the root of a SharePoint site.)
   
   
    
3. Proceed to log in - Use Microsoft Authentication. “Sign in” if you haven’t done so. (This process is for the initial time only.) If the window doesn't appear and you receive an error message, follow the steps in the "Access to the resource is forbidden" section on the PBI Troubleshooting page.
   
4. Here, you will see all of the files across all of the SharePoint Libraries to which you have access. Select the file you need and click ‘Transform Data’.
   

Filter

The list contains all of the files in the SharePoint library to which you have access, so you may need to filter them. There are a few different ways to filter files:

• Filter by the "Folder Path" 
  
   
• Filter by the "Name" column. You can use the text filter function or check the boxes next to the file name.
  
  
   
• Filter by the "Extension" column.  Choose the file type you are looking for.
  
  

Combine

1. Click on the ‘Combine’ button on the "Content" field. (All files in the folder should be homogeneous in format and structure)

   

2. This will generate a Combine Files window. If the files are in .xlsx format, navigate to the needed sheet/table under Display Options to generate the preview and click ‘OK’. Files in csv format will already be in auto preview. Power BI combined two files into a single table and added a Source. Name column that contains information about the original file source for each row in the table. 

3. Add any additional steps/operations to the dataset if needed. Query Editor will auto-generate steps on the right-hand side of the pane, which will help with troubleshooting later.

4. Select ‘Close and Apply’ to finish loading the data and return to the report view mode. 

External Sources

• Combine files (binaries) in Power BI Desktop (From Microsoft)
• Understanding The “The key didn’t match any rows in the table” Error In Power Query In Power BI Or Excel (From Chris Webb's BI Blog)