Upsert (update) capabilities in Power Apps Dataflows

[EricRegnier]

Power Apps Dataflows needs to support upsert (insert and/or update). Currently Dataflows only supports insert/create and delete operations (if enabled). This means that every time a Dataflow is ran will insert duplicate records. If an alternate key is defined then those records will fail during the run because of duplicate key. If delete records is enabled, then all records will be deleted and created per run. There are several keys issues with this:

1. Upon delete, all history and auditing history are lost
2. Harder to determine records that changed after a Dataflow run. Modified On will always be the Created On 
3. Unnecessary triggers (i.e. plugins, flows, workflows, etc) could be invoked upon create and/or delete
4. Performance: additional redundant delete and create transactions are executed when nothing changed

[CatalinAdler]

I second this.

But in addition it also has to support entity delete: what if one entity was deleted in the import source or you decide that you have to deleted it based on rules in dataflow itself?

the existing delete capabilities in dataflows are impractical as they will delete all entities which are not visible in the import data flow at the moment of execution, which forces us to always import everything: this cannot scale in any way.

[mattyn07]

I third this.

The idea behind a dataflow is fantastic and aligns with Microsofts strategy regarding simplicity and ease of use when working with the Power Platform however this is a massive massive gap and until this is addressed a dataflow is quite limited.

[rgsiiiya]

Interestingly, the PowerBI dataflow supports "incremental refresh" for all the obvious reasons stated:

Using incremental refresh with dataflows | Microsoft Docs

While the Power Apps/Dataverse new Export to Data lake goes a long way toward exporting/updating bulk data...

There needs to be the inverse, some way to bulk import/update data!

Here are some common use cases we encounter that we would use this for!

- Updating currency exchange rates

- updating stock values

- updating inventory values

etc.

Power Apps/Dataverse dataflow would benefit from the exact same functionality for all the stated reasons in the PBI doc.

[TomMDL]

I was astouned to read that DataFlow only supports insert/create, and not upsert.
That limits the use to initial one time data imports... due to no update possibility...

Not that useful after all then...

[crmkeeper]

Being able to control Update, Create or even Upsert makes total sense. While we wait, I guess there is always KingsWaySoft:)

[Terrable]

Hi, maybe this was implemented after this suggestion, but if the destination table has key(s) created, it will upsert records now. 

[testasdfasfsafa]

No one from Microsoft every read this forum... since not much gets ever "done".  The Power Platform doesn't get much attention or love, major gaps everywhere, sad. So much potential..

[jaeiow]

@testasdfasfsafa there are thousands of factors that go into not testing a community idea.

[EricRegnier]

@testasdfasfsafa @jaeiow I can see how it can be dissapointing that it might not look like Microsoft is looking at these but rest assure they are slowly doing it. There's just a large volume accross different channels to look at the ideas. There are aware there's lots of room for improvement monitoring these and hopefully it will be better eventually.

@heaher_italent FYI, maybe you can ellaborate on the ideas monitoring process? Thanks 🙂

[heaher_italent]

@EricRegnier - thank you for the @mention.  I will make sure this gets to the right part of our Power Apps Team.  The Ideas are monitored and Ideas are always welcome.  We appreciate all of you for being part of the community.

Heather

Community Admin

Power Apps