Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
RKM
Helper IV
Helper IV

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

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?

9 REPLIES 9
parry2k
Super User
Super User

@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?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

col1col2col3Last Updated at
AAA2022-01-01 15:30:00
B  2022-01-01 15:30:00

 

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

CCC2022-01-24 18:00:00

 

Now today I have following 3 records

BBB2022-01-25 10:00:00
CC1C22022-01-25 10:00:00
DDD2022-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?

 

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.

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
Not applicable

@RKM make sure you have Power BI premium license.

 

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

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

parry2k
Super User
Super User

@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 YouTube.gif 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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

As I understand, incremental refresh does not upsert.  It refreshes (deletes and re-inserts) a partition of records representing a date range, based on date field values on those records.

This would not support a scenario where an arbitrary record (possibly in a historical partition) needs to be updated because its corresponding data in a source system has changed.  Power BI (Power Query) doesn't appear to support manipulation of existing records during refresh, other than "keep it or delete/re-import it, based on date" (i.e. incremental refresh).

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors