Connect to Multiple Data Source Files

Body

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’.

    Power BI window that shows Get Data from SharePoint folder option

  2. Enter this URL: https://umainesystem.sharepoint.com/sites/UMSPowerBIUsersCommunity_O365 (The URL has to be the root of a SharePoint site.)

    box for entering the URL on Power BI Desktop window
     
  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.
    Showing sign in box for SharePoint access in Power BI Desktop
  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’.
    SharePoint file list and showing the location of transform data button

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" 
    showing folder path oprion when narrowing down the list of files
     
  • Filter by the "Name" column. You can use the text filter function or check the boxes next to the file name.

    Filter by name oprion on Power BI desktip
     
  • Filter by the "Extension" column.  Choose the file type you are looking for.

    Extention filter oprion when narrowing down the list of files

Combine

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

    showing the location of icon for Content combine function in Power BI

  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.Query settings dialogue box with each applied steps listed

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

      location of close and apply icon on query editor in Power BI desktop   

External Sources

Environment

  • Power BI

Details

Details

Article ID: 159294
Created
Fri 5/10/24 4:43 AM
Modified
Thu 6/26/25 8:59 AM
Applies To
Staff