Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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?
@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
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?
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?
@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.
@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.
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).
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
94 | |
90 | |
35 | |
35 |
User | Count |
---|---|
153 | |
99 | |
82 | |
63 | |
54 |