Configuring Incremental & Scheduled Refresh

Yes, that setup is fine. The warning in the docs applies specifically to integer surrogate keys like 20240801, not to real date columns like the one you have. Converting a Date column to Date/Time in Power Query is the standard supported path for incremental refresh and normally folds against relational sources such as SQL Server, Azure SQL, or Fabric Warehouse.

To confirm folding on your specific source, right-click the last step in Power Query and choose View Native Query. If you can see the underlying SQL, folding is still intact. Then apply the RangeStart and RangeEnd filter with Table.SelectRows and check View Native Query again to confirm the filter pushes down to the source.

If this helped, a thumbs up and accepting the solution would be appreciated.

Best,
Shai Karmani

Hi RossS​,

Your date column already looks much closer to what Incremental Refresh expects.

The important thing isn't just that the column displays as a Date/Time in Power Query. The key requirement is that the filtering step using RangeStart and RangeEnd still folds back to the source.

A simple way to verify this is:

1. Create the RangeStart and RangeEnd parameters as Date/Time.
2. Apply your incremental refresh filter using those parameters.
3. Right-click the filtered step in Power Query and check "View Native Query" (if your source supports it).

If View Native Query is still available after applying the filter, that's a good sign that query folding is being preserved.

One thing to be careful about is where the Date/Time conversion happens. If your source column is already a true date or datetime type in the database, changing the type in Power Query is usually fine. However, if Power Query is having to perform a complex conversion on every row before applying the RangeStart/RangeEnd filter, folding can break and incremental refresh may not work efficiently.

My recommendation would be to validate query folding after the filter step rather than relying solely on the displayed format. If folding is preserved, you're generally good to proceed with incremental refresh and scheduled refresh configuration.

Hi RossS 

If you go to official microsoft documentation.they have provided a mechanism to convert datetime to any datekey format.

https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview 

below is the example i am talking about.

let
  Source = Sql.Database("dwdev02","AdventureWorksDW2017"),
  Data  = Source{[Schema="dbo",Item="FactInternetSales"]}[Data],
  #"Filtered Rows" = Table.SelectRows(Data, each [OrderDateKey] >= Int32.From(DateTime.ToText(RangeStart,[Format="yyyyMMdd"]))),
  #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [OrderDateKey] < Int32.From(DateTime.ToText(RangeEnd,[Format="yyyyMMdd"])))
  
in
  #"Filtered Rows1"

Like INt32 converts it to numeric for numericdate column.you can convert datetime column to date like below.

let
  Source = Sql.Database("dwdev02","AdventureWorksDW2017"),
  Data  = Source{[Schema="dbo",Item="FactInternetSales"]}[Data],
  #"Filtered Rows" = Table.SelectRows(Data, each [OrderDate] >= RangeStart),
  #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [OrderDate] < RangeEnd)
in
  #"Filtered Rows1"

Please give kudos or mark it as solution once confirmed.

so RangeStart and RangeEnd datatype remains datetime.

Hi RossS​,

Have you had a chance to review the solution shared by Shai_Karmani​ Praful_Potphode​ ShivekMaharaj​ ? If the issue persists, feel free to reply so we can help further.

Thank you.

Hi RossS​,

Checking in to see if your issue has been resolved. let us know if you still need any assistance.

Thank you.

That's fine. The warning in the docs is about converting integer surrogate keys (yyyymmdd) to date/time, which breaks folding because it needs a full table scan to reinterpret the integers. Your column is already a proper date column, so converting it to date/time is a normal type change that folds against most sources (SQL Server, Synapse, etc.)

I've added steps in Power Query to limit my date:

= Table.SelectRows(Item_Ledger_Entries_Excel_table, each DateTime.From([Posting_Date]) >= RangeStart and DateTime.From([Posting_Date]) < RangeEnd)

I've also set Incremental Refresh policies for the table, archiving data to 2 years and incrementally refresh data starting 3 days ago.

Refresh in Power BI desktop works fine, I publish to service, refresh and it appears to be loading but fails.:

Data source errorDataSource.Error: OData: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.. Microsoft.Data.Mashup.ValueError.DataSourceKind = OData. DataSourcePath = https://api.businesscentral.dynamics.com/v2.0/3243243fsdfdsfsd/Production/ODataV4/Company('CompanyName')/ItemLedgerEntries. . The exception was raised by the IDataReader interface. Please review the error message and provider documentation for further information and corrective action.

Any idea where to go from here?

Hi RossS​,

If that column is genuinely typed as Date/Time in Power Query, then a value such as 01/08/2024 00:00:00 is fine. The visible formatting itself does not prevent incremental refresh.

The important part is that RangeStart and RangeEnd are created as Date/Time parameters and that the table is filtered using those parameters. Microsoft's incremental refresh configuration guidance only requires the DateTime-to-integer conversion when the source stores the date as an integer surrogate key such as 20240801.

I would also verify that the filter step still folds back to the source. If View Native Query remains available after applying the RangeStart/RangeEnd filter, that is a good sign the source can handle the partition filtering efficiently.

So based on the example you showed, I would not convert that Date/Time column to an integer unless the underlying source actually uses an integer date key.

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

It's quite confusing when multiple people respond to my question, saying similar things but not reading what other people have written.

I will check suggestions once our system has been moved to SAS next month and reply then.