SQL Data refresh for new data only

[TeroK]

I'm using SQL server for importing data to Power Bi, and I refresh the new data after each month. Problem is that I would like update only the latest month and not the older data as it has not been changed and it has been imported already. Data refresh takes a lot of time (over 8hours) and it increases after each month naturally as there is more data.  I cannot use incremental refresh as I don't want to publish the report. Reason being that i cannot do changes for it locally anymore and I cannot download it as pbix.

I know that I can query the data month by month but that is a no-go as well as I loose all the created measures and I have redo those after each refresh. Neither cannot I combine the data in report side as then the file size becomes the problem as power BI seems to duplicate the data which feels kinda weird.

Would appreciate info whether there is a way to do this? 

[amitchandak]

@TeroK , not very clear. Have you implemented an incremental load efficiently

https://radacad.com/all-you-need-to-know-about-the-incremental-refresh-in-power-bi-load-changes-only
https://thinkaboutit.be/2020/02/how-do-i-implement-an-incremental-refresh-in-power-bi-free-or-pro/
https://blog.crossjoin.co.uk/2020/04/13/keep-the-existing-data-in-your-power-bi-dataset-and-add-new-...
https://www.nabler.com/articles/power-bi-data-refresh-and-scheduling/
https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/
https://www.fourmoo.com/2020/03/11/how-to-configure-incremental-refreshing-in-power-bi-with-datekey-...

[TeroK]

ok, sorry for not being clear enough. I'll try to depict it better:
Query data from on-premise SQL server which contains data from January 2020, Power Bi imports the data to my local Power bi report. "Apply query changes" is done for January

Same query (refresh) which contains now data from January to February 2020, Power Bi imports the data to my local Power bi report.

"Apply query changes" is done over January and February and so on. I hope you get what I mean. After every month the query becomes slower as there is more legacy data.

Incremental refresh cannot be used as I don't want to publish the report which is a mandatory step If I have understood correctly.  And union cannot be used in report side as it doubles the pbix file size as I wrote earlier.

[v-yingjl]

Hi @TeroK ,

Since you refresh data from SQL, I would suggest you to use DirectQuery mode to query instead of import mode, it will send query to the data source directly and the data model will not be too large. You can referr this document about import and direct query mode.

In addition, you can try to use query folding to optimize you query to avoid refresh and query fail, refer these document and article that hopes to help you:

1. Query folding guidance in Power BI Desktop 
2. Query Folding 

Best Regards,
Yingjie Li

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