How To Load Daily Data Incrementally

[mfarsln]

Hello,

I have two tables. One is for past days and one is for today. Only difference between two tables is the date column. All the remaining columns are same. And refreshing for Table2 is disabled. It just loaded once.

So what i want to do is taking today's values from first table and when it's no longer today, add those rows to second table. For example; when i refresh my whole data model, Table1 holds values for 12 December 2019 while Table2 holds 11,10,9 December.

Appending these two tables won't work since today's values changes everyday and table2 doesn't save those values either. If i append two tables today, final table would contain 9-10-11-12 December values. But when i refresh it tomorrow, it will become 9-10-11-13 December. 

In short, i just want to load data incrementally/cumulatively, just like in qlik sense. I know there is an incremental refresh option in pro version but i don't have it unfortunately.

Thanks in advance

[artemus]

I'm not clear on your model.

Does the current day table change its name?

Do you want to know how to add a date column to table1 with the current day, then do the append?

Is there an issue having a dynamic reference to the current day?

[mfarsln]

Let me put an easy example of my target.

This is my daily data table which only gets current day results. Its coming from sql and refresh enabled.

date

18.12.2019

And this is my second table which has loaded just once. Data in this table doesn't change so it's not included in data refreshing.

date

15.12.2019

16.12.2019

17.12.2019

And think this table as my target goal

If i refresh table1 and then append it to table2 it seems working. It gets true result for first 4 rows on the above table. But if i refresh this model at 19 December, final result will be like this;

date

15.12.2019

16.12.2019

17.12.2019

19.12.2019

Since the first table gets daily results, it just deletes 18.12.2019. 

Hope this will make the point more understandable. I can create a simple app if you want.

[artemus]

If you don't use incremental refresh, then all the tables which are set to include in refresh are fully repopulated. For this reason, you would need to include the first table in the refresh, so you get the previous day. From here you just append current day. If you have a lot of historice data you can put a cutoff where that data doesn't load.

[mfarsln]

Thanks for your answers.

Isn't there any way to tell power query to not overwrite with append, just add new rows to the table? If the history table has 18 December values from previous append, keep it then append newly refreshed 19 December values and keep this logic going on for further days. This is a key technique, it shouldn't be that much challenging on pbi.

[v-juanli-msft · Accepted solution]

Hi @mfarsln 

If your data source(Sql Server) changes data daily,

eg, today it has data at 2019/12/19, tomorrow it has data at 2019/12/20, data for 2019/12/19 would be replaced by 2019/12/20,

In this case, when refreshing from Power BI, It would import data for 2019/12/20.

"Refresh" in Power BI is a complete refresh, it would import all new data from the data source.

Incremental refresh which needs a higher license may fit your requirement.

Besides, there are some workarounds without a higher license.

https://www.youtube.com/watch?v=u7SyqTpLIJc

https://www.thebiccountant.com/2016/02/09/how-to-create-a-load-history-or-load-log-in-power-query-or...

http://excel-inside.pro/blog/2019/03/21/classical-incremental-refresh-for-cloud-data-sources-in-powe...

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.