Setup Dataflows to Upsert to an Existing Table

(0) Share

Report

Posted on by rprahalad

377

Hello All,

Right now I have set up a few complicated Power Query transformations and have the "upsert" Dataflows in place. These Data Flows picks up Excel files dropped every night and Upserts the DataVerse tables.

But why is there no option to pick alternate keys (used for upserts) when I choose "Load To Existing Dataverse Table" as the choice (when setting up Data Flows).

Because I need to define custom column names on the Dataverse side and set up my Data Flows to upsert to it.

How can I pick alternate keys and set up Data Flows to upsert to an existing Dataverse table?

Thanks

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Verified answer

EricRegnier 8,720 Most Valuable Professional on at

Like (1)

Report

Hi @rprahalad,

Not sure I fully understand your issue.... Assuming your alternate keys are already defined, are they active? To see if they're active, use the classic solution explorer: navigate to your solution in make.powerapps.com --> Switch to Classic button in the command bar --> expand the entity --> expand Keys. If it's inactive that probably why you can't see it.

Was this reply helpful? Yes No
rprahalad 377 on at

Like (0)

Report

@EricRegnier ,

Thanks as always for replying! When I create a table in Dataverse, I usually rename 'Name' to ID as my Primary Key. But I did not know a way to create alternate keys. This is great! Yes, I have been through this old interface a ,lot and it never stuck me that I can create 'alternate keys' here.

I can't wait to try this out and appreciate your help again!

Was this reply helpful? Yes No
rprahalad 377 on at

Like (0)

Report

@EricRegnier , I had another question with regards to Dataverse. I have created a bunch of tables for Phase 1 release. As part of the release, I have to port the existing production data (maintained in excel and other files) to these Dataverse tables and I am downloading excel templates and massaging the data to fit the Datavese schema and importing it over.

So, for phase 1 its all INSERTS into these brand new dataverse tables and so, it should go fine. But come phase 2, we would be adding/designing more columns to these existing production tables.

How can we port the current excel data for those columns into these Dataverse tables without actually messing up the already entered data?

Is it best to write a canvas app then and loop the existing excel data for those new columns and ensure we upsert it accordingly or is Data Flows still the best option?

IN SQL Server I could have just written an Update script but here, I don't know the best practices to release a product in phases.
Thanks

THanks

Was this reply helpful? Yes No
EricRegnier 8,720 Most Valuable Professional on at

Like (1)

Report

If you don't have a lot of data and prefer to use Excel then that's a good option. Export your current data in Excel from Dataverse, perform your messaging there for phase and reimport. Dataverse will perform updates and inserts accordingly. More info on Excel import: https://docs.microsoft.com/powerapps/maker/data-platform/data-platform-import-export

Hope this helps!

Was this reply helpful? Yes No
rprahalad 377 on at

Like (1)

Report

Thanks @EricRegnier ! Worked like a charm. I can't believe that I never once thought of creating the alternate key beforehand:-). I always was thinking in terms of "choosing and picking" the alternate key while setting up the Data Flow.

You were right. Once I created the alternate key at the Dataverse table level, the key showed up when I set up the Data Flow and I could 'upsert' the excel data to point to an existing table.

Was this reply helpful? Yes No
rprahalad 377 on at

Like (0)

Report

Thanks @EricRegnier !

Was this reply helpful? Yes No