Forum Discussion
Advocate IIIncremental refresh for dataflow gen2 and the BC connector
I have to edit my post below by stating that I found out microsoft did wrote a post on incremental refresh patern without the incremental refresh using append:
https://learn.microsoft.com/en-us/fabric/data-factory/tutorial-setup-incremental-refresh-with-dataflows-gen2
=======
original post below
=======
With a dataset I managed to enable an incremental refresh for BC using the instructions in the link below
Since this organizations has multiple companies I also used a function to loop though the companies and managed to do incremental refreshes and combining data of multiple companies. While the datetime column is then no longer in the table it does not break the logic of the incremental refresh of the dataset.
After this I tried to simulate the same within a dataflow gen2 using this documentation
https://learn.microsoft.com/en-us/fabric/data-factory/dataflow-gen2-incremental-refresh#limitations
This time however using exactly the same code I failed to activate the incremental refresh. The error stated: You cannot set incremental refresh for a query with no DateTime columns.
While using the same code in the tested setup of a dataset, it seems crazy that this is not allowed in the dataflow gen2. Technically it is correct to state there are no datetime columns in this query, but they are used to invoke the function, although I have to convert them to date to be able to filter the connector of the BC API
The invoke of the function
let
Source = Companies ,
#"Invoked custom function" = Table.AddColumn(Source, "Invoked custom function", each LoopGLE([BCCompany],fxDate(RangeStart),fxDate(RangeEnd))),
#"Expanded Invoked custom function" = Table.ExpandTableColumn(#"Invoked custom function", "Invoked custom function", shortened here where all the columns I needed})
in
#"Expanded Invoked custom function"
The function
let
Source = (varcompany as any, RangeStart as date, RangeEnd as date) => let
Source = Dynamics365BusinessCentral.ApiContentsWithOptions(null, null, null, [UseReadOnlyReplica=true]),
PRODUCTION = Source{[Name="PRODUCTION"]}[Data],
#"varcomp" = PRODUCTION{[Name=varcompany]}[Data],
WebServices = #"varcomp"{[Name="WebServices"]}[Data],
General_Ledger_Entries_Excel_table = WebServices{[Name="General_Ledger_Entries_Excel",Signature="table"]}[Data],
#"Filtered Rows" = Table.SelectRows(General_Ledger_Entries_Excel_table, each [Posting_Date] >= RangeStart and [Posting_Date] <= RangeEnd)
in
#"Filtered Rows"
in
Source
Another issue that I came across that I had to set the datalake destination to replace instead of what I expected, append. Since I only want to add, why should I replace? Trying replace gave the error “It is not supported to setup incremental refresh on a query when: its data destination update method is set to “append””
Since I want to collect multiple years I ended up with the bucket of a quarter as the smallest possible bucketsize, that does not feel like real incremental loading anymore.
Then during the deployment I got the last and final error “Failed to verify that a query with incremental refresh is folding” even disabling the checkbox on Require incremental refresh query to fully fold did not help to solve that issue. That gave me the feeling the BC connector is not properly showing queryfolding capabilities to the dataflow.
Towards a working solution
I ended up solving this issue by using a different approach.
I first ran the collection of all records of the G/L table out of BC and added them to a deltalake table that used Append. Then I created a second dataflow gen2 that collected the latest entry no. from the deltatable using the datawarehouse endpoint (hey good old SQL is my everlasting friend). Now slightly adjusted my first dataflow gen2 that did first a full load and added to the function a parameter that said latest_entry_no. Now every subsequent run does an append for all entries greater than the last entry created. Since G/L tables do not change this for me feels like a safe approach. It also took down the runtime of the query substantially. But I am curious to see if I overlooked something and why Microsoft does not state such solutions to the Incremental refresh documentation. Cause in this way even if the source is not capable of queryfolding at least the writing part takes less time. On top of that we are no longer limited to bucket sizes or date ranges.
Interested to see your feedback.
Hi SofBL
After going through your post, as per my thinking in your case, the key difference is that incremental refresh in a dataset and in a Dataflow Gen2 are enforced differently by the engine. In a Power BI dataset, the RangeStart and RangeEnd parameters only need to exist logically in the M query pipeline, and as long as the final query folds and the filter logic is applied somewhere upstream,even if the datetime column disappears later,the incremental refresh policy can still activate. However, Dataflow Gen2 enforces the rule at the final query stage: it requires the DateTime column to physically remain in the output table when configuring incremental refresh. Because your looping function expands company data and removes the date field before the final output, Dataflow Gen2 treats it as lacking a valid incremental column, even though function inputs use RangeStart and RangeEnd. That is why the same M logic works in a dataset but fails in Dataflow Gen2.
Additionally, Dataflow Gen2 currently does not allow append mode with incremental policies because the lakehouse engine expects to completely manage partitions for incremental loads. It needs replace mode so it can rewrite partition files; otherwise, append mode would lead to inconsistent partitions. This also explains the quarterly bucket size limitation, the engine needs predictable partition logic. The folding error you encountered further confirms that the Business Central connector still has folding limitations within Dataflow Gen2, even if it folds properly in a dataset, so the Fabric engine does not trust the query for incremental partition pruning.
I think by loading the full BC dataset once into Delta Lake, then using a second Dataflow Gen2 to append only rows with higher entry numbers,is a very practical pattern. It bypasses the datetime requirement, avoids folding constraints, and simulates incremental load efficiently by key-based tracking. This approach is commonly used in data engineering pipelines for immutable ledger-type tables, so you did not miss anything,in fact, you implemented a correct pattern where date-based incremental refresh isn’t suitable. Microsoft does not promote this pattern in official docs because incremental refresh in Fabric is positioned as a date-driven, fold-dependent mechanism, whereas key-based delta ingestion is an engineering workaround outside the standard UI-driven model. But in real-world Business Central workloads with multiple companies and immutable entry tables, your entry-no-based incremental ingest is arguably more efficient and scalable.
3 Replies
- Poojara_D12
Super User
Hi SofBL
After going through your post, as per my thinking in your case, the key difference is that incremental refresh in a dataset and in a Dataflow Gen2 are enforced differently by the engine. In a Power BI dataset, the RangeStart and RangeEnd parameters only need to exist logically in the M query pipeline, and as long as the final query folds and the filter logic is applied somewhere upstream,even if the datetime column disappears later,the incremental refresh policy can still activate. However, Dataflow Gen2 enforces the rule at the final query stage: it requires the DateTime column to physically remain in the output table when configuring incremental refresh. Because your looping function expands company data and removes the date field before the final output, Dataflow Gen2 treats it as lacking a valid incremental column, even though function inputs use RangeStart and RangeEnd. That is why the same M logic works in a dataset but fails in Dataflow Gen2.
Additionally, Dataflow Gen2 currently does not allow append mode with incremental policies because the lakehouse engine expects to completely manage partitions for incremental loads. It needs replace mode so it can rewrite partition files; otherwise, append mode would lead to inconsistent partitions. This also explains the quarterly bucket size limitation, the engine needs predictable partition logic. The folding error you encountered further confirms that the Business Central connector still has folding limitations within Dataflow Gen2, even if it folds properly in a dataset, so the Fabric engine does not trust the query for incremental partition pruning.
I think by loading the full BC dataset once into Delta Lake, then using a second Dataflow Gen2 to append only rows with higher entry numbers,is a very practical pattern. It bypasses the datetime requirement, avoids folding constraints, and simulates incremental load efficiently by key-based tracking. This approach is commonly used in data engineering pipelines for immutable ledger-type tables, so you did not miss anything,in fact, you implemented a correct pattern where date-based incremental refresh isn’t suitable. Microsoft does not promote this pattern in official docs because incremental refresh in Fabric is positioned as a date-driven, fold-dependent mechanism, whereas key-based delta ingestion is an engineering workaround outside the standard UI-driven model. But in real-world Business Central workloads with multiple companies and immutable entry tables, your entry-no-based incremental ingest is arguably more efficient and scalable.
v-dineshyaCommunity Support
Hi SofBL ,
Thank you for reaching out to the Microsoft Community Forum.
Hi Poojara_D12 , Thank you for your prompt response.
Hi SofBL , Could you please try the proposed solution shared by Poojara_D12 ? Let us know if you’re still facing the same issue we’ll be happy to assist you further.
Regards,
Dinesh
- SofBL
Hi thanks for reminding me Dinesh, I was expecting also alternatives to my aproach. I like the response of Poojara as it encourages me to continue. Maybe it was just my feeling that It takes more than one swallow to make a summer.
I will accept his solution but truly apreciate other perspectives and suggestions.