Import data from a folder with multiple files (Power Query) (2024)

Keep it simple:

• Ensure that all the files you want to combine are contained in a dedicated folder without extraneous files. Otherwise, all files in the folder and any subfolders you select are included in the data to be combined.

  See Also

  Import data from JSON filesJSON to EXCEL

• Each file should have the same schema with consistent column headers, data types, and number of columns. The columns do not have to be in the same order as the matching is done by column names.

• If possible, avoid unrelated data objects for data sources that can have more than one data object, such as a JSON file, an Excel workbook, or Access database.

Each of these files followa simple pattern, only one table of data in each file.

1. SelectData> Get Data > From File > From Folder. The Browse dialog box appears.

2. Locate the folder containing the files you want to combine.

3. A list of the files in the folder appears in the <Folder path> dialog box.Verify that all the files you want are listed.

   

4. Select one of the commands at the bottom of the dialog box, for exampleCombine > Combine & Load. There are additional commands discussed in the section About all those commands.

5. If you select any Combine command, The Combine Files dialog box appears. To change file settings, select each file from the Sample File box, set the File Origin, Delimiter, and Data Type Detection as desired. You can also select or clear the Skip files with errors checkbox at the bottom of the dialog box.

6. Select OK.

Result

Power Query automatically creates queriesto consolidate the data from each file into a worksheet. The query steps and columns created depend on which command you choose. For more information, see the section, About all those queries.

1. SelectData> Get Data > From File > From Folder. The Browsedialog box appears.

2. Locate the folder containing the files you want to combine.

3. A list of the files in the folder appears in the <Folder path> dialog box.Verify that all the files you want are listed.

4. Select one of the commands at the bottom of the dialog box, for exampleCombine > Combine & Transform. There are additional commands discussed in the section About all those commands.

   The Power Query Editor appears.

5. The Value column is a structured List column. Select the Expand

   

   icon, and then select Expand to New rows.

   

6. The Valuecolumn is now a structured Record column.Select the Expand

   

   icon. A drop-down dialog box appears.

   

7. Keep all the columns selected. You may want to clear the Use original column name as a prefix check box. Select OK.

8. Select all the columns that contain data values.Select Home, the arrow next to Remove Columns, and then select Remove Other Columns.

9. Select Home > Close & Load.

Result

Power Query automatically creates queriesto consolidate the data from each file into a worksheet. The query steps and columns created depend on which command you choose.For more information, see the section, About all those queries.

For more flexibility, you can explicitly combine files in the Power Query Editor by using the Combine Files command. Let’s say the source folder has a mixture of file types and subfolders, and you want to target specific files with the same file type and schema but not others. This can improve performance and help simplify your transformations.

1. Select Data > Get Data > From File > From Folder. The Browse dialog box appears.

2. Locate the folder containing the files you want to combine, and then select Open.

3. A list of all the files in the folder and subfolders appears in the <Folder path> dialog box. Verify that all the files you want are listed.

4. Select Transform Data at the bottom. The Power Query Editor opens and displays all the files in the folder and any subfolders.

5. To select the files you want, filter columns, such as Extension or Folder Path.

6. To combine the files into single table, select the Content column that contains each Binary (usually the first column), and then select Home > Combine Files. The Combine Files dialog box appears.

7. Power Query analyzes an example file, by default the first file in the list, to use the correct connector and identify matching columns.

   To use a different file for the example file, select it from the Sample File drop-down list.

8. Optionally, at the bottom, select Skip files with errors to exclude those files from the result.

9. Select OK.

Result

Power Query automatically creates a queriesto consolidate the data from each file into a worksheet. The query steps and columns created depend on which command you choose.For more information, see the section, About all those queries.