Incremental refresh and historic data from multiple sources

Hi mch98 

Mixing a "cold" archive (weekly files) with a "hot" daily feed is common, and Incremental Refresh will behave only if you set it up on one foldable query.

Here are the steps:

1. Stage the two sources

• HistoricFiles -folder of weekly CSV/XLS/etc. (disable load)

• CurrentFeed -SQL view/table (disable load)

Make their schemas match (same columns, data types, names).

2. Create the final query by referencing, not duplicating

let
    Historic   = HistoricFiles,   // already cleaned
    Current    = CurrentFeed,     // already cleaned
    Combined   = Table.Combine({Historic, Current}),
    // RangeStart / RangeEnd are DateTime parameters you created
    Filtered   = Table.SelectRows(Combined, each [YourDateColumn] >= RangeStart and [YourDateColumn] < RangeEnd)
in
    Filtered

Only this Combined query is loaded. Put Incremental Refresh on it.

3. Order of steps matters
The date filter (using RangeStart/RangeEnd) must be the last step before the output. Anything after that can break folding and kill IR.

4. First publish trick
If the historic chunk is huge, publish once without IR, run one full refresh, then enable IR and republish. That keeps the first policy refresh small.