Forum Discussion

Powerwoman's avatar
Powerwoman
Helper II
1 year ago
Solved

Incremental refresh in Dataflows using Business Central

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: ...
Need Help
  • Powerwoman's avatar
    Powerwoman
    1 year ago

    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's avatar
v-venuppu
Community Support
1 year ago

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's avatar
Powerwoman
Helper II
1 year ago

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.