Incremental Refresh sourced from SQL server and only add new rows to an already modified table

[whgenie]

Hi

I tried searching for answers but can't seem to find out that fits what I'm trying to do.

Currently I have a table in SQL server that auto refreshes daily. I use Power Query to load this table as my source (call this Table A) manually as a Query Connection initially. Note: Somtimes in Table A, there could be data changes in old records after refreshing so Table A is always getting the most up to date data (existing and new records) from the SQL server table.

I have another table (call this Table B) that I merge with Table A to add more data columns (and data) and now I have Table C. Note: Table B will always have new data as well but the merge will find the right row to add to make Table C.

Table C will then have its data modified or entire rows deleted or new rows added by users.

How can I append only new rows from Table A from the last refresh date/time so that it can merge with Table B to append into existing Table C? This refresh is done manually on Power Query.

Note:

1) Data changes in previous already loaded rows are not needed and ignored so they do not overwrite Table C

2) All modified data on Table C must to be retained and not overwritten from the incremental refresh (there could be data modified, new rows or deleted rows in Table C by users)

Any guidance/advise is very much appreciated! Thank you!

[lbendlin · Accepted solution]

It's called "incremental refresh"  for a reason. It expects immutable data and is not what you need (which would be differential refresh).

Incremental Refresh works on the level of partitions, not on rows.  The lowest operation you can do is flush and fill a partition.  Usually this is the latest partition, but with XMLA tools you can also refresh any other partition.

The Power BI service will not care about data duplicates across partitions. Avoiding or eliminating duplicates is entirely your own responsibility.

[v-xinruzhu-msft · Accepted solution]

Hi,

Thanks for the solution lbendlin  offered, and i want to offer some more information for user to refer to.

hello @whgenie , as lbendlin  mentioned,  there's no way to keep historical data in Power BI, and once your original data is refreshed, Power BI will only keep your most recent data, not your previous data. and Incremental refresh will only retain the data in the interval after the refresh, and cannot retain the complete data. You can refer to the following link.

Incremental refresh for semantic models in Power BI - Power BI | Microsoft Learn

 If you want to get the new rows from Table A from the last refresh date/time, and to keep all the data in C, it is better that you create a history table in SQL to keep the history data in table c, then import the data to power BI.

Best Regards!

Yolo Zhu

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

[lbendlin · Accepted solution]

It's called "incremental refresh"  for a reason. It expects immutable data and is not what you need (which would be differential refresh).

Incremental Refresh works on the level of partitions, not on rows.  The lowest operation you can do is flush and fill a partition.  Usually this is the latest partition, but with XMLA tools you can also refresh any other partition.

The Power BI service will not care about data duplicates across partitions. Avoiding or eliminating duplicates is entirely your own responsibility.