Dataverce Power Automate

[PWPL]

I have a customer database where records are added or data in records is changed every day. This database will be used in a Power App in a lookup field.

What is the simplest way to upload a new database from an Excel file to Dataverse daily using Power Automate? And if I keep the database name the same, can this new one be used in the lookup field? Or do I first have to delete all the rows in the table and upload them again?

[v-jefferni]

Hi @PWPL ,

You need to keep the Dataverse table but clear it every day and then upload data from Excel to this table. However, since it's used for a LookUp column in another table, if you delete old rows from this table, LookUp columns in that table will be cleared as well. Generally speaking, we usually use a main table with static values as the source for a LookUp column in another table, I have no idea why you use a table with all different rows every day as the look up table, could you explain this with more details?

Best regards,

[PWPL]

The second table that changes daily contains customer data. New customers are added every day, and corrections are made to the "old" records, such as updating phone numbers or correcting addresses. That's why I can't rely solely on adding new rows to the table in Dataverse.

[v-jefferni · Accepted solution]

Hi @PWPL ,

You need to add new rows for new customers and edit old rows for corrections. If you delete old rows then data stored in LookUp column of the other table will be missing. You can use a flow, list rows in the Excel table, and use a Condition to check if a customer is already in the table. If yes, update the row to "correct" it, if no, add a new row to the table.

Detail in Update a row action

Best regards,

[Fubar]

Possibly consider using a Dataflow rather than Flow as it will do an UPSERT if configured correctly.

e.g.

• if you have a unique identifier in your source data, create a field for that field in your target dataverse table.
• on the Dataverse table create an Alternate Key on that field
• Configure a Dataflow
  • Source data
  • Target data
  • Mapping fields source-target
  • Set the key as the unique field

When the dataflow runs (can be triggered manually or on a schedule) it will do an Update if the key field matches, insert it if they key field value is not found