Power Query Folder Imports: Validate Every File First

Before combining a folder of Excel workbooks in Power Query, validate each file against a small contract: the expected Excel table exists, required columns are present, and every required value is usable. Keep a file audit, then make the reporting query raise an error when any file fails. A successful refresh that quietly omits a workbook can produce an incomplete report.

This guide uses native Power Query M for Excel or Power BI Desktop and .xlsx workbooks containing a named Excel table called Sales. It focuses on validating input files before appending them. For a refresh that fails only after deployment, use the Desktop versus service refresh diagnostic.

Define the file contract before clicking Combine

Write down the folder, reporting period, expected file names, table name, required fields, parsing culture, and what happens on failure. In this example, SaleID must be nonblank and Amount must convert to a finite number using en-US. Negative amounts remain valid, allowing credits. Empty tables block the batch. Extra columns are allowed but deliberately excluded from the output.

Compare column names, not just their count. Replacing Amount with SalesAmount preserves a two-column count while breaking the contract. Exact matching also makes an invisible space in a header visible as a missing-column failure. Do not silently rename unfamiliar headers until the source owner confirms their meaning.

Microsoft documents that Table.Combine can use the union of input schemas. Missing fields can therefore appear as nulls in the combined result. Appending tables is not proof that each file supplied the fields your report needs.

Build a three-file test batch

Create three fictional workbooks in a separate test folder. Format each range as an Excel table and name it Sales using Table Design. Store SaleID as text, including any leading zeros. Use the following data; each pair represents one data row.

The initial inventory has three files and five rows. One file containing two rows passes; two files containing three rows are held. Only one row has a demonstrated invalid value. The missing-column file has not passed value validation, so its invalid-row count is unknown, not zero. The reporting query must return an error for this batch.

Create the per-file audit query

Create a blank query named FolderAudit, open Advanced Editor, and paste this M. Set RootFolder to your absolute Windows folder path, keeping the final backslash. The exact folder-path filter intentionally excludes subfolders. Folder.Files otherwise includes files recursively. Temporary Excel lock files starting with ~$ and extensions other than .xlsx are excluded before workbook processing.

let
    RootFolder = "C:\Imports\WeeklySales\",
    Required = {"SaleID", "Amount"},
    AuditOne = (content as binary, sourceName as text) as record =>
        let
            Opened = try Excel.Workbook(content, null, true)
                {[Item="Sales", Kind="Table"]}[Data]
        in
            if Opened[HasError] then
                [Status="unreadable", Rows=null, InvalidRows=null,
                 Detail=Opened[Error][Message], Data=null, BadRows=null]
            else
                let
                    Raw = Opened[Value],
                    Missing = List.Difference(Required, Table.ColumnNames(Raw)),
                    RowCount = Table.RowCount(Raw)
                in
                    if List.Count(Missing) > 0 then
                        [Status="schema", Rows=RowCount, InvalidRows=null,
                         Detail="Missing: " & Text.Combine(Missing, ", "),
                         Data=null, BadRows=null]
                    else
                        let
                            Selected = Table.SelectColumns(Raw, Required),
                            Indexed = Table.AddIndexColumn(
                                Selected, "SourceRow", 1, 1, Int64.Type
                            ),
                            WithRaw = Table.DuplicateColumn(
                                Indexed, "Amount", "AmountRaw"
                            ),
                            Typed = Table.TransformColumnTypes(
                                WithRaw,
                                {{"SaleID", type text}, {"Amount", type number}},
                                "en-US"
                            ),
                            Checked = Table.AddColumn(Typed, "Validation", each try
                                if [SaleID] = null or [Amount] = null then false
                                else Text.Trim([SaleID]) <> ""
                                    and (Value.Is([AmountRaw], type number)
                                        or Value.Is([AmountRaw], type text))
                                    and not Number.IsNaN([Amount])
                                    and Number.Abs([Amount]) <> #infinity,
                                type record
                            ),
                            Failed = Table.SelectRows(Checked, each
                                if [Validation][HasError] then true
                                else not [Validation][Value]
                            ),
                            BadCount = Table.RowCount(Failed),
                            Evidence = Table.SelectColumns(
                                Failed, {"SourceRow", "SaleID", "AmountRaw"}
                            ),
                            Output = if BadCount > 0 or RowCount = 0 then null else
                                Table.AddColumn(
                                    Table.SelectColumns(Typed, Required),
                                    "SourceFile", each sourceName, type text
                                )
                        in
                            [Status=if RowCount = 0 then "empty"
                                     else if BadCount > 0 then "values" else "ready",
                             Rows=RowCount, InvalidRows=BadCount,
                             Detail=if RowCount = 0 then "Empty Sales table"
                                     else if BadCount > 0 then "Inspect BadRows" else "OK",
                             Data=Output, BadRows=Evidence],
    Files = Folder.Files(RootFolder),
    InScope = Table.SelectRows(Files, each
        [Folder Path] = RootFolder
        and Text.Lower([Extension]) = ".xlsx"
        and not Text.StartsWith([Name], "~$")
    ),
    Inputs = Table.SelectColumns(InScope, {"Name", "Content"}),
    CheckedFiles = Table.AddColumn(
        Inputs, "Audit", each AuditOne([Content], [Name]), type record
    ),
    Expanded = Table.ExpandRecordColumn(
        CheckedFiles, "Audit",
        {"Status", "Rows", "InvalidRows", "Detail", "Data", "BadRows"}
    ),
    Result = Table.RemoveColumns(Expanded, {"Content"})
in
    Result

Excel.Workbook reads the binary and selects the named table, rather than guessing which worksheet is relevant. delayTypes=true defers type assignment. A named Excel table already supplies column names; do not promote its first data row as another header. Worksheet imports need a separately reviewed header-promotion step.

The function checks Table.ColumnNames before selecting and converting fields. Table.TransformColumnTypes uses the explicit culture. Each row receives a try record; conversion errors and failed required-value checks both count as invalid. The check rejects dates and logical values in Amount even if a conversion could interpret them numerically. It also rejects null amounts, blank identifiers, NaN, and infinity.

Amounts are general numbers here. Financial precision, allowed decimal scale, currency codes, and acceptable ranges need explicit business rules. Converting an Excel numeric identifier to text cannot restore leading zeros Excel already discarded. Validate the source template as well as the import.

Inspect failures without deleting evidence

In FolderAudit, review Name, Status, Rows, InvalidRows, and Detail. For west.xlsx, open its nested BadRows table: expect SourceRow=2, SaleID=W2, and AmountRaw=pending. SourceRow counts data rows inside the Sales table, not physical worksheet row numbers.

The numeric conversion target and raw Amount are separate columns, so the diagnostic retains the original value. Keep the unchanged input workbook with your batch receipt. A missing table, unreadable workbook, or unsupported protected workbook is reported as unreadable when caught at selection; other step errors still stop the query. Ask the owner for a supported readable export instead of bypassing protection.

Microsoft's error-handling documentation explains the HasError, Value, and Error fields. This query records failures and blocks release. Replacing an error with zero, null, or an empty table would change that policy and could conceal missing sales.

Block the combined output until every file passes

Create a second blank query called SalesCombined. Keep FolderAudit as a connection or disable its model load because it contains nested tables. Load SalesCombined as the reporting table.

let
    Audit = FolderAudit,
    Rejected = Table.SelectRows(Audit, each [Status] <> "ready"),
    Result =
        if Table.RowCount(Audit) = 0 then
            error Error.Record("FileGate", "No in-scope files found.", null)
        else if Table.RowCount(Rejected) > 0 then
            error Error.Record(
                "FileGate", "Import blocked; inspect FolderAudit.",
                Table.SelectColumns(Rejected, {"Name", "Status", "Detail"})
            )
        else
            Table.Combine(Audit[Data])
in
    Result

The reporting query checks the audit before calling Table.Combine. No in-scope files is an explicit failure. Any schema, value, empty-table, or unreadable-file failure blocks the batch. It does not select only the ready files and label the remainder a successful import. Unexpected query errors also propagate.

With the initial fixture, expect two rejected files and a FileGate error. Correct the source: rename south's header to Amount after confirming its meaning, and replace west's pending with the owner-confirmed value 25. Refresh both queries. Expect three ready files, five combined rows, zero invalid rows, and Amount total 100: north 30, south 30, west 40. Loading only north would have shown 30 and concealed 70.

Add the checks a real batch needs

A folder scan cannot know that an expected workbook never arrived. Compare file names against an approved batch inventory and check the reporting period before release. Validate row counts and a control total against the source owner's receipt. Add duplicate SaleID checks across the combined table if the contract requires uniqueness; per-file validation alone cannot detect cross-file duplicates.

Use a closed batch folder: producers finish writing files elsewhere, then hand off the complete batch. Do not edit workbooks while refresh is reading them. Query references do not establish an immutable snapshot or guarantee a shared cached evaluation. Large folders may be read more than once; measure refresh cost on representative data and move heavy ingestion to an appropriate staging process if needed.

On failure, preserve the previous successful report and show its actual batch date so readers can distinguish stale data from a current result. Arrange an operational alert and an owner for rejected files. When moving a local-folder source to the service, verify the supported gateway route and its file permissions using the deployment diagnostic linked above.

Verify the gate in your own Power Query host

The fixture arithmetic and acceptance expectations were checked separately. The native M queries were reviewed against Microsoft documentation but were not executed in a Power Query host for this article. Run them against copies of your three workbooks in your Excel or Power BI version before adapting the shared report.

Test the initial failure, both source corrections, an empty folder, an empty Sales table, a null Amount, and an extra optional column. The original bad batch, empty folder, empty table, and null Amount must block release; the corrected batch and extra-column case must retain the expected row count and total. Then test the actual scheduled execution path. Save the input inventory, audit, expected totals, and refresh timestamp together.

FAQ

Why not use Skip files with errors?

Skipping changes which source files contribute to the result. If completeness matters, reject the batch and identify the failed file. Partial delivery needs an explicitly approved policy and a visible completeness measure.

Can I normalize header spaces automatically?

Only after defining the allowed mapping and checking for collisions. Two distinct headers can become the same name after trimming or case changes. Preserve original names in the audit and reject ambiguous mappings.

Does a ready file prove the business totals are correct?

No. It passes this file contract. Missing files, wrong periods, duplicate transactions, incorrect units, and plausible but wrong amounts still require batch reconciliation and business checks.