Question

How do I combine multiple Excel files from a folder as part of an automated ETL process?
I have recurring Excel files stored in folders and need an automated way in Power BI to combine and transform them consistently with minimal manual steps.

Answer 1

To combine multiple Excel files from a folder in Power BI automatically:

1. Use “Get Data” > “Folder”

   • Connect to the folder containing all Excel files.

2. Combine Files

   • Power Query shows a sample file preview.

   • Use the Combine Files button to automatically extract and append data from all files with the same structure.

3. Transform Consistently

   • Apply your transformation steps (e.g., filter, rename columns) on the combined query.

   • These steps apply to every file in the folder automatically.

4. Refresh Automatically

   • When new files are added to the folder, refreshing the dataset in Power BI will pick them up and append the data without manual intervention.