Incremental refresh in Dataflows using Business Central

[Powerwoman]

Hello everybody,

I'm trying to set up an incremental refresh in Dataflows (PPU).
It let's me do all the set up steps correctly

But the refreshing time is way to long for 7 days of data:

I am using the Microsoft Business Central Connector for BC Cloud, am seeing the same behaviour if I use the odata connector.

It seems like the date filter (rangeStart, rangeEnd) isn't working and Power BI is always trying to fetch all data.

Thanks a lot!

[v-venuppu]

Hi @Powerwoman ,

As we haven’t received any further updates and there are no outstanding queries at the moment, we will proceed with closing this thread for now. If you have any additional questions in the future, please don’t hesitate to start a new thread we’re always here to help.

Thank you.

[v-venuppu]

Hi @Powerwoman ,

Thank you for sharing the solution shared by Microsoft.

kindly consider marking it as "Accept as Solution".This helps others in the community who may face a similar problem to find it easily.

Thank you.

[v-venuppu]

Hi @Powerwoman ,

This happens when query folding is broken.

If your connector (e.g., Dynamics365BusinessCentral.ApiContentsWithOptions) doesn’t support folding beyond certain steps, filters are applied after fetching all data, which defeats the purpose of incremental refresh.

Here is an recommended approach to solve the issue:

1.Use OData.Feed() connector instead of ApiContentsWithOptions.

This allows query folding to the Business Central source.

Folding is necessary for RangeStart and RangeEnd filters to be effective.

2.Apply date filters directly using RangeStart and RangeEnd:

3.Avoid transformations before the filter step that could break query folding.

How to confirm whether it works or not:

Right-click the filtering step in Power Query and check if “View Native Query” is enabled.

If it’s greyed out, folding is broken and incremental refresh won’t behave correctly.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly.

Thank you.

[Powerwoman]

Dear @v-venuppu ,
tried your approach, but even in the first step it doesn't give me the native query option.

I also contacted Microsoft, this is the answer I got:
"Unfortunately, our OData stack (on which APIs are built on) in Business Central does not support this."

😔

[v-venuppu]

Hi @Powerwoman 

Thank you for reaching out to Microsoft Fabric Community.

Your current setup in the incremental refresh UI looks correct. However, due to a lack of query folding, Power BI ends up downloading all rows instead of just the incremental range.

To fix this, you need to explicitly apply the RangeStart and RangeEnd filters in Power Query (M code) and ensure that query folding is happening. Without folding, the filters are not pushed down to Business Central, and incremental refresh won’t behave as expected.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[Powerwoman]

Dear @v-venuppu ,

thanks for coming back to me so quickly.
RangeStart and RangeEnd are included in the M Code:
let
Quelle = Dynamics365BusinessCentral.ApiContentsWithOptions("TNG-Production", null, null, [UseReadOnlyReplica = true, Timeout = null, ODataMaxPageSize = null, AcceptLanguage = "de-de"]),
#"Navigation 1" = Quelle{[Name = "Company"]}[Data],
#"Navigation 2" = #"Navigation 1"{[Name = "Advanced"]}[Data],
#"Navigation 3" = #"Navigation 2"{[Name = "powerBIConnector/v1.0"]}[Data],
#"Navigation 4" = #"Navigation 3"{[Name = "glEntries", Signature = "table"]}[Data],
#"Geänderter Spaltentyp" = Table.TransformColumnTypes(#"Navigation 4", {{"systemModifiedAt", type datetime}}),
#"glEntries-73797374656D4D6F6469666965644174-autogenerated_for_incremental_refresh" = Table.SelectRows(#"Geänderter Spaltentyp", each DateTime.From([systemModifiedAt]) >= RangeStart and DateTime.From([systemModifiedAt]) < RangeEnd)
in
#"glEntries-73797374656D4D6F6469666965644174-autogenerated_for_incremental_refresh"

I also tried to put the filters in the OData.Feed logic but then it wouldn't let me safe the dataflow saying this is a dynamic source.