DARTS : Connect to Multiple Data Source Files

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 tab on each Excel file should be the same as well. If you have files which structures are different, please go to Connect to Data Source file page.

Get Data
Filter
Combine
Advanced Section

Get Data

In Power BI Desktop, select Get Data > More > SharePoint folder and click ‘Connect’.
Enter this URL: https://umainesystem.sharepoint.com/sites/UMSPowerBIUsersCommunity_O365 (The URL has to be root of a SharePoint site.)
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 show up and give you an error message, follow the steps in "Access to the resource is forbidden" section on PBI Troubleshooting page.
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 "Name" column. You can use 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.
Filter by the "Folder Path"

Combine

Click on the ‘Combine’ button on the "Content" field. (All files in the folder should be homogeneous in format and structure)
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 be already in auto preview.
Power BI combined two files into the single table and added a Source.Name column that contains information about the original file source for each row in the table.
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 for troubleshooting later.
Select ‘Close and Apply’ to finish loading the data and return to the report view mode.

Advanced Section

Can I connect to multiple files in the folder without combining them?

No, if the files have different structure and/or extensions.

-> Use the Connect to a Single Data Source file method. Then copy the queries and change the steps to connect to a different file instead or go to the Recent Connections and start over.

Yes, if the files have the same structure.

After you follow the Edit>Filter>Combine Steps, use ‘Group By’ the Source.Name column. Choose ‘All Rows’ in the Operation dropdown then click ‘OK.’
Right Click on the Table cell under Count Column and select Add as New Query:
Repeat this step for both rows and you will see two new Queries appear with the source file names, then right click on the Combined Dataset in the queries list (it will have a folder name) and un-check ‘Enable load.’
‘Close and Apply’.

HELP IS AT HAND!

Link to compose email for help with website.

Question, comment, critique? We are here to help and we strive to make our site a comprehensive, user friendly experience. Contact us at DARTS@maine.edu.

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)