Power BI File-Based Incremental Refresh Failure at Month Change — Root Cause & A Robust Fix

 How to Prevent Power BI File-Based Incremental Refresh from Failing When a Partition Has No Files (Month Change Issue)

1. Background

Incremental refresh in Power BI is one of the most important performance and scalability features when dealing with large datasets. Instead of reloading the entire dataset every time, incremental refresh:

• splits data into partitions (typically monthly or daily)

• refreshes only the newest partitions

• archives older partitions

This works extremely well for database sources, but teams often run into unexpected failures when applying incremental refresh to file-based sources (Folder / SharePoint / SFTP folders) such as:

• daily CSV transaction extracts

• Excel files exported by upstream processes

• flat-file snapshots of operational systems

• vendor feeds delivered into network folders

---

2. Problem Statement

Symptom

Power BI dataset refresh fails in the Power BI Service / Gateway with an error similar to:

There weren't enough elements in the enumeration to complete the operation.
(MashupDataAccessError / Microsoft.Data.Mashup.ValueError / Expression.Error)

In practice, this frequently occurs at month change, for example:

• Today is Feb 1

• incremental policy refreshes Feb partition

• Power BI applies partition filter 2026-02-01 to 2026-02-28

• but the folder still contains files only up to Jan 31

• result: folder query returns 0 files

• Power Query tries to combine / expand data anyway and crashes

---

3. Why This Happens (Root Cause Analysis)

3.1 Incremental refresh with files behaves differently

When incremental refresh is enabled for a table, Power BI automatically generates partitions using the RangeStart and RangeEnd parameters.

In a database incremental refresh scenario, this is easy:

WHERE TxnDate >= RangeStart AND TxnDate < RangeEnd

If the database returns 0 rows, that’s valid:
✅ refresh succeeds, no data loaded.

But for file-based sources, the refresh flow often looks like:

1. List files (Folder.Files)

2. Filter only needed files (extension, naming convention)

3. Derive file date (from name or metadata)

4. Apply incremental filter to files

5. Combine files (invoke custom function / expand table columns)

6. Type conversion / rename / select columns

The key issue:

Folder-based queries frequently assume at least one file exists.

3.2 The hidden “{0} first row / sample file” dependency

Power BI’s “Combine Files” pattern relies on:

• #"Sample File ...", and

• Table.ColumnNames(#"Transform File..."(#"Sample File ..."))

When there are zero files, Power Query cannot reference:

• first file

• sample file

• transform function output schema

This produces the gateway error:

not enough elements in the enumeration

---

4. Why the Problem Does NOT Occur for Database-Based Incremental Refresh

Database sources naturally support:

• empty result sets

• schema stability

• query folding into SQL

• predictable evaluation paths

File sources often do not:

• schema is inferred from sample file

• file list may become empty

• Power Query expand steps require at least one row/file

Therefore:
✅ Database incremental refresh → stable
⚠️ File incremental refresh → fragile during “no file” partitions

---

5. The Correct Expected Behavior

From a business and refresh perspective, when a partition has no file:

✅ Refresh should succeed
✅ Dataset should remain valid
✅ Incremental pipeline should continue
✅ New partitions should be ready to accept future files

Power BI refresh should treat “no files” similar to “no rows”.

---

6. The Solution: Handle “No Files” Case Explicitly

High-level design

We introduce a clean pattern:

1. Define an empty output table with correct schema

2. Wrap the “normal processing” section into a variable RealDataOutput = let ... in ...

3. Use a final switch:

Output = if Table.IsEmpty(#"Incremental loading") then EmptyDataOutput else RealDataOutput

This is a safe, minimal-change approach that allows developers to:

✅ build normal query as usual
✅ add only 3 reusable sections
✅ avoid breaking incremental refresh
✅ stop the gateway crash

---

7. Sample Implementation Template (Recommended Pattern)

Below is a reusable template.
Developers can keep their normal ETL steps unchanged.

---

✅ Power Query Script Template: “3 Sections No-Files Handling”

let

//====================================================
// (1) Handle no files: EMPTY OUTPUT (schema only)
//====================================================
    EmptyDataOutput =
        #table(
            type table [
                Column1 = text,
                #"Column 2" = number,
                #"Column 3" = text,
                FileDateTime = datetime
            ],
            {}
        ),

//====================================================
// Normal steps (develop as usual)
//====================================================
    Source = Folder.Files("\\...\Source Data"),

    #"Filtered Rows - Cleanup" =
        Table.SelectRows(Source, each Text.StartsWith([Name], "xyz_") and [Extension] = ".csv"),

    #"Added FileDateTime" =
        Table.AddColumn(
            #"Filtered Rows - Cleanup",
            "FileDateTime",
            each DateTime.From(Date.FromText(Text.Select([Name], {"0".."9"}))),
            type datetime
        ),

    #"Incremental loading" =
        Table.SelectRows(
            #"Added FileDateTime",
            each [FileDateTime] >= #"RangeStart" and [FileDateTime] < #"RangeEnd"
        ),

//====================================================
// (2) Handle no files: REAL OUTPUT (normal logic)
//====================================================
    RealDataOutput =
        let
            #"Invoke Custom Function" =
                Table.AddColumn(
                    #"Incremental loading",
                    "Transform File",
                    each #"Transform File xyz"([Content])
                ),

            #"Removed Other Columns" =
                Table.SelectColumns(#"Invoke Custom Function", {"FileDateTime", "Transform File"}),

            #"Expanded Table Column" =
                Table.ExpandTableColumn(
                    #"Removed Other Columns",
                    "Transform File",
                    Table.ColumnNames(#"Transform File xyz"(#"Sample File xyz"))
                ),

            #"Changed Type" =
                Table.TransformColumnTypes(
                    #"Previous step",
                    {
                        {"Column1", type text},
                        {"Column 2", type number},
                        {"Column 3", type text},
                        {"FileDateTime", type datetime}
                    }
                ),

            QueryOutput =
                Table.SelectColumns(
                    #"Changed Type",
                    {"Column1", "Column 2", "Column 3", "FileDateTime"}
                )
        in
            QueryOutput,

//====================================================
// (3) Handle no files: FINAL SWITCH
//====================================================
    Output =
        if Table.IsEmpty(#"Incremental loading") then EmptyDataOutput else RealDataOutput

in
    Output

---

8. Why This Works (Technical Explanation)

When there are no files

• #"Incremental loading" is empty

• Table.IsEmpty(#"Incremental loading") returns true

• query immediately returns EmptyDataOutput

• “combine files” logic never executes

When there are files

• normal block runs as expected

• output schema matches EmptyDataOutput

• incremental refresh can generate partitions without failure

---

9. Best Practices (Strongly Recommended)

✅ 9.1 Make empty schema exactly match final output

Your EmptyDataOutput must match:

• column names

• data types

• column count/order (preferably)

If not, Power BI can throw “schema mismatch” errors.

---

✅ 9.2 Derive FileDateTime from file name carefully

This pattern is common but risky:

Text.Select([Name], {"0".."9"})

Because it might collect digits from:

• file version number

• sequence number

• other metadata

Better:

• enforce naming convention

• parse exact substring

• validate with try ... otherwise null

---

✅ 9.3 Keep the incremental filter step simple

Ensure the partition filter is applied only on the file-date column:

each [FileDateTime] >= RangeStart and [FileDateTime] < RangeEnd

---

✅ 9.4 Don’t use {0} / first file logic in incremental refresh

Avoid:

FilteredFiles{0}[Content]

Because it will always crash on empty partitions.

---

10. Final Summary

File-based incremental refresh in Power BI can fail during month transitions because Power Query’s “Combine Files” logic assumes the folder contains at least one file.

✅ The fix is simple and scalable:

• Define an empty output table

• wrap normal processing in a RealDataOutput let block

• return empty table when file list is empty

This approach:
✅ prevents refresh failure
✅ preserves incremental refresh behavior
✅ keeps query design clean
✅ requires minimal change to existing scripts