Combine a folder of monthly files without surprises

Drop each month’s export into a folder, click Refresh, and you have one table. Get Data → From Folder can replace a lot of copy-paste. It also has a few traps, and they’re easy to prevent.

1. Filter the folder first

A folder often contains more than you expect. While someone has a workbook open, Excel creates a hidden lock file starting with ~$, which can get picked up and fail the refresh. Stray files with other formats cause trouble too.

Filter right after the source step:

let
    Source = Folder.Files(FolderPath),
    OnlyExports = Table.SelectRows(
        Source,
        each not Text.StartsWith([Name], "~$")
            and Text.Lower([Extension]) = ".xlsx"
    )
in
    OnlyExports

Text.Lower matters because Power Query comparisons are case-sensitive, so .XLSX wouldn’t match otherwise.

2. Make the folder a parameter

The FolderPath above is a parameter, created under Manage Parameters. If the folder moves, or a colleague takes over the file, you change it in one place instead of editing the query.

3. Keep the source file name

When you combine files, keep the Source.Name column. You’ll want it the first time a total looks wrong, because it tells you which file each row came from. For an audit trail, it’s free.

Then tie it out

After each refresh, check two things against what you expect:

• Row count per file: group by Source.Name. A file with far fewer rows than usual may be a partial export.
• Total per file: compare it to the source system’s control total.

A combine that runs without errors tells you the files were read, not that they were complete.