Power BI Incremental Load - Require Clarity. Does it merge /UPSERT or simply amend?

[RKM]

I was probably over-excited about this Incremental Refresh feature but now after reading some article here and there, I'm feeling a bit nervous, is it really refreshing data or just amending records?

I need some expert help here please... I have a very large table with a column called (last updated). I thought of using that column for incremental refresh with Settings as 1Year Data Archive, 1Day Data Incremental Load. So for say, on 24th Jan 22 -- Power BI suggesting it will archive 24th Jan 21 to 23rd Jan 22.   And Then incremenatal refresh scope is after or equal 2022-01-24 00:00:00 and before 2022-01-25 00:00:00

~ Upto here its fine, but what if my data of 20th Jan - lets say now gets updated and we get a new update timestamp of 24th-Jan-2022, then incremental refresh certainly will pull the record but what next? Will it be able to delete the old and insert new (UPSERT /MERGE) ? Or Now PBI will create 2 records? If 2 records then its totally wrong for my scenario. Any idea how to avoid such situation?

[parry2k]

@Anonymous premium is not required for an incremental refresh. IR works in Pro license as well but if you have premium, great.

@RKM  What is your 3rd question?

[RKM]

Hi @parry2k 

Question was - How does PBI identifies which row to merge/upsert and not create a new row. I.e. How does it identifies any Composite PK etc.? Lets say my Archive partition has got following records

col1	col2	col3	Last Updated at
A	A	A	2022-01-01 15:30:00
B			2022-01-01 15:30:00

Now Let's say yesterday night's daily incremental picked another record

C

C

C

2022-01-24 18:00:00

Now today I have following 3 records

B	B	B	2022-01-25 10:00:00
C	C1	C2	2022-01-25 10:00:00
D	D	D	2022-01-25 11:00:00

~ So tonight's refresh, (as per my wish) shold create a new record with Col1 = D; where as it should update 2 existing records for Col1 = B or Col1 = C

How does that happen? How does Power BI identify which record of past (be it is in Archive Partition or Yesterday's Incremental partition) to update (and not create duplicate insert?

[V-lianl-msft]

Hi @RKM ,

If you have a modified DateTime (or updated DateTime) in your table, then the process of incremental refresh can monitor that field, and only get rows that their date/time is after the latest date/time in that field in the previous refresh. To enable this process, you can enable the Detect Data Changes, and then choose the modified date or update date from the table. https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview#optional-setting... 

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

[RKM]

Apologies again. I think the vive of my question is getting lost with lot of answers about how to enable it. I am aware how to enable it.

My question was - for say, I have IR configured as 1Year Archive, 7Days IR and then lets say some record in database got updated where previous modified day was 6months back (i.e. Data was pulled and placed in PBI archive partition), and now new modified date came in today. So ofcourse PBI will fetch it but will it be able to replace that old record from Archive? Or it will create new row in Lastest partition. If we can replace old row, then how? How it will know what's primary key etc?

[Anonymous]

@RKM make sure you have Power BI premium license.

Regards,
John Basha Mattipati,
https://www.linkedin.com/in/john-basha-mattipati-06812980/

[RKM]

I do. Thanks. But looking for answer of pending part of my query (Message 3) please.

[parry2k]

@RKM not two records, otherwise it is a BIG FAILURE and nobody will be able to use it, it will refresh the partitions based on the IR policies.

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[RKM]

Thank god. and thanks @parry2k 

Feeling a little safe after your answer, but what is that incremental refresh policy here actually and how it detects which record has changed etc.?  I mean if I expect it act like, Merge or Upsert, where do I define, a COMPOSITE PRIMARY KEY kind of thing so that it understands which record to update essentially (and Not Create a New). Thanks in advance for your help please