How to set up incremental refresh based on custom conditions?

Hi UBP,

Did you manage to run the incremental to see if it worked? I think the logic should be correct.

Thinking your dataset as being stored in two types of "boxes" (partitions):

1. Archived Partitions (e.g., last 3 years): This contains all your historical data (e.g., data for 01_11_FCST that was loaded in March). During a refresh, Power BI does not touch, query, or delete this data. It is safe.
2. Refresh Partition (e.g., last 10 days): This is the only "box" your M script runs against. Power BI completely replaces this partition every day.

Your M script correctly tells Power BI how to build that new "Refresh Partition" efficiently.

How Your Script Works Correctly:

• On 20th March: The script tells Power BI, "For the last 10 days, query the source for FCST_WRKG AND 01_11_FCST." This data is loaded into the model.
• On 5th May: The script tells Power BI, "For the last 10 days, query the source for FCST_WRKG AND 02_10_FCST."

At this point, the data for 01_11_FCST (loaded in March) is now in an archived partition. It is no longer in the 10-day refresh window, so it is safe and will not be deleted.

Your script is performing exactly as intended: it is preventing Power BI from wasting time querying the data source for 01_11_FCST data in May, because you have correctly told it that no new data for that category exists. You are not losing data; you are just performing an efficient, conditional query for the newest partition.

The Final M Script (British English Comments)

This is the correct script to use in your Power Query transformation *before* applying the incremental refresh policy.

let
    // Connect to your data source
    Source = YourDataConnectionSource,

    // 1. Define categories that must refresh daily
    DailyRefreshList = {"FCST_WRKG", "BDGT_WRKG", "BDGT_PRIM1"},

    // 2. Get the current date to determine conditional logic
    CurrentDate = Date.From(DateTime.LocalNow()),
    CurrentYear = Date.Year(CurrentDate),

    // 3. Determine which special forecast to include in the query TODAY
    ForecastToIncludeToday =
        if CurrentDate >= #date(CurrentYear, 3, 15) and CurrentDate <= #date(CurrentYear, 4, 1) then
            "01_11_FCST"
        else if CurrentDate >= #date(CurrentYear, 4, 15) and CurrentDate <= #date(CurrentYear, 5, 1) then
            "02_10_FCST"
        // ... add other 'else if' conditions here
        else
            null, 

    // 4. Build the final list of categories to query at the source
    CategoriesToKeep = DailyRefreshList & List.RemoveNulls({ForecastToIncludeToday}),

    // 5. CRUCIAL: Filter the SOURCE data based on the categories to refresh today.
    // This step is highly efficient and (if possible) will be "Query Folded"
    // (e.g., becoming a WHERE clause in SQL).
    FilteredByCategory = Table.SelectRows(Source, 
        each List.Contains(CategoriesToKeep, [Plan_Category])
    ),

    // 6. FINAL: Apply the standard Incremental Refresh date filter.
    // Power BI manages the RangeStart and RangeEnd parameters.
    FilteredByDate = Table.SelectRows(FilteredByCategory,
        each [Plan_Date] >= RangeStart and [Plan_Date] < RangeEnd
    )
in
    FilteredByDate

✅ If this response solved your problem, please mark it as correct to help other community members.