Custom incremental Dataflow refresh

[Anonymous]

Hi,

I have unique requirement from my clients to implement Incremental dataflow refresh for a specific year. please read below to understanding more in detail. Any suggestions is greatly appreciated.

I have created a data flow with 7 identical tables (one table for each year from 2015 to 2021) by appending them using Power query online, each table contains 40+ columns & 1Million records for each year.  So the total records in the dataflow after complete refresh is 7M, currently the entire refresh process is taking around 4.5 to 5 hours. 

But the requirement is:- For example consider 2017 data got changed. Database team will truncate the "Dataset_2017" table only and reload it back with new data. Now users want to implement a feature in dataflow for just deleting the 2017 data from the dataflow and refresh it back for only that specific year instead of complete refresh from 2015 to 2021. Is there anyway we can implement this in Dataflow refresh process, ultimate goal of the clients is to reduce the amount of time it takes to refresh. Since dataflow already has got data for other years (in this case 2015, 2016, 2018, 2019, 2020, 2021) they do not want to refresh these years again and want to refresh only changed year.

Can anyone provide some insights on how to implement this using power query or incremental feature in power bi.

[TomMartens]

Hey @Anonymous ,

as dataflows support incremental refresh, the concept is different from what you are looking for, this article explains the concept of incremental refresh for dataflows: https://docs.microsoft.com/en-us/power-query/dataflows/incremental-refresh

You might consider to create a custom application by leveraging the Power BI Rest API: https://docs.microsoft.com/en-us/power-query/dataflows/incremental-refresh

Depending on your design/architecture/licensing you might be able to leverage DirectQuery, nevertheless, you will need to combine all dataflows into a single entity, this will still take some time.

Hopefully, this provides some additional insights/ideas to tackle your challenge.

Regards,

Tom

[Anonymous]

Hi Tom,

Thank you for taking time to check on the question and sharing your ideas.

Direct query is not a option for us as user wants this report to give them results in very less time. Direct query can take time to read from database and render the report with output.

Incremental refresh might not work in our case, I know incremental feature with Power BI can only append latest data at the end if dataflow transformations can fold back to database. I explained about incremental refresh capabilities to the client but still they are looking if we can implement something to delete and reload a specific year that got changed.

If you have some example of custom application using Rest API, can you point me towards that to see if that works.