Importing data from folder with multiple files using Power Query

Use Power Query to combine multiple files with the same schema stored in a single folder into one table. For example, each month you want to combine budget workbooks from multiple departments, where the columns are the same, but the number of rows and values differ in each workbook. Once you set it up, you can apply additional transformations as you would with any single imported data source and then refresh the data to see results

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

3. Select Transform Data at the bottom. The Power Query Editor opens and displays all the files in the folder and any subfolders.
To select the files you want, filter columns, such as Extension or Folder Path.

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

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.

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

Select OK.

[brianlasta]

You saved my life, sir. Thank you very much.