Incremental refresh with several Appended Dataflows

[iBusinessBI]

I have an SQL Server datasource (accounting system) which I need to combine with Excel data in Sharepoint (consolidated journal entries that are not inside the accounting system).

I need to have this data appended to a single Fact table in my dataset. 

The accounting system data is changing only in the last 4 months, the rest is not changing.

I am trying to implement an Incremental Refresh but combining two different datasources - SQL and Sharepoint.

I've created 3 Dataflows: Recent Data, Historical Data and Excel Data, which suits my needs and refreshes very well.

But how can I Append them all in the dataset, while keeping the Query Folding of the dataflow?

I've tried this: https://blog.crossjoin.co.uk/2021/08/22/how-query-folding-and-the-new-power-bi-dataflows-connector-c...

But as soon as I Append the queries, I loose the Folding...

Please help

[iBusinessBI · Accepted solution]

I have found the solution! First I’ve created three queries from the new Dataflow connector.

Then I’ve added the filtering with RangeStart and RangeEnd parameters in each query.

I’ve checked and the query folding is taking place in all queries. Then I’ve disable loading into model for the three queries and created a forth query (FactTable) which Appends the three. Then I filtered this table again with the RangeStart and RangeEnd parameters (this filter is redundant but we need it, otherwise the model will not allow us to set incremental refresh policy). On this step the query folding is lost but it doesn’t matter since we already append the filtered data from the previous, folded queries. The last step is of course to set the incremental refresh policy and publish. This can also be implemented for different data sources, for example - SQL Server and Dataflow. Hope someone can also benefit from this solution.

[datapal04]

Hi, can this solution work for CSV files being used in an appended query? I have a CSV file with historical data. I have a connection to Dataverse with the same columns but more recent data. I appended the queries. How can I set up incremental refresh?

[Phillipe_soares]

Hello, I use in a dataset some data flow, such flows are already configured for incremental update in the power bi service.
Only that it would be necessary to configure the incremental update in the set.

I'm finding several difficulties for this, would it be possible for you to help me?

I ran it exactly as mentioned in your post, but without success.

[otravers]

In this scenario I'd give Power BI Premium serious consideration to get access to linked entities.

[iBusinessBI]

@ImkeF Imke, maybe you have a solution to this challenge since it is both an accounting setup and has an incremental refresh challenge?