Advanced Excel · Intermediate level

Intermediate level · Module 1: Power Query for Data Preparation · Lesson 3 of 3

Folder Imports and Parameters

Import every file in a folder automatically and make queries flexible with parameters.

In this lesson you will

  • Combine all files in a folder
  • Use parameters for paths and settings

Key concepts

From Foldercombine filesparametersrefresh and error handling

The lesson

Get Data > From Folder combines every file in a folder using the same steps. Next month, drop the new file in the folder and refresh — no copy-and-paste.

Keep source file names in a column so every row can be traced to its file.

Parameters store values such as the folder path or the reporting year, so the workbook can be moved or reused without editing queries. Check refresh errors and handle files that break the expected format.

Step by step

  1. Combine the twelve monthly files from a folder.
  2. Keep the source file name column.
  3. Create a folder-path parameter.
  4. Add a new file and refresh.
Step-by-step

Twelve monthly files into one report with Power Query

The steps that replace a day of copy-and-paste with one refresh each month.

  1. Collect the files in one folder

    Each branch saves its monthly file (same layout) into a shared folder such as Reports\2026.

  2. Get Data > From Folder

    Point Power Query at the folder and choose Combine & Transform; it applies the same steps to every file.

  3. Clean once

    Remove title rows, promote headers, delete total rows, trim names and set data types — each action becomes an applied step.

  4. Keep the source

    Keep the file-name column so every row can be traced back to its branch file.

  5. Merge reference data

    Merge the Branches table on the branch code to add region and manager.

  6. Load and refresh

    Load to a table or the data model. Next month: drop the new file in the folder and press Refresh All.

Result

One tidy table of every month and branch, rebuilt identically each month in seconds, with no manual copying errors.

Practice activity

Complete the folder-based import from the practical activity.

Expected result: Adding a file and refreshing updates the combined table correctly.

Check your understanding

  1. How does a folder import handle next month's file?

    Show answer

    Put it in the folder and refresh.

  2. Why use a parameter for the folder path?

    Show answer

    So the workbook can be moved without editing the queries.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.