I had to build a Power Automate flow.
There is no "upsert" CDS function, so I had to create a flow that looks at an Excel table and cycles through the records, for each row it queries the database to see if the record alread exists. If so, then update. If not, then insert.
This is so incredibly complicated for a tool that Microsoft is trying to put into the hands of the user! It took a few hours to learn how to put it all together correctly, and changes to the data are a pain. Tell me that any of my users would be able to do this, even with guidance.
Setup your import file:
- Create an Excel file in Teams. Save the file in a channel.
- Add columns and data
- Format as a table
- Name the table the name of your table to help keep it straight.
Import data via Excel in Dataverse for Teams:
- Open Power Apps in Teams
- Click the Build tab.
- Select your team and click See all in the Built by this team panel.
- Click the Flows page on the left and then click New.
- Create a new flow, from manual trigger.
- Add a step - Excel: List rows present in a table
- Point the step to the sharepoint site for the team
- Point the Document library to the library in the team
- Choose the file. I called mine "data load".
- Select the table that was named above.
- Add a step - Apply to each
- Output from this step is the Excel value
- Add a step inside the apply to each - CDS List records. Look for an existing record with the filter.
- Set the Entity name to the name of the Dataverse table
- Show advanced options
- Set Filter query to be the key column of the table, equal to, and set to column from your Excel table. The name of the column in the Dataverse table is not the same as the description. Open the table to see all the columns with the actual column name. My "Name" column was actually named "cre4f_name". "eq" mean equals. And then surround the Excel field from the previous step with single tick marks. The result looks like this:
- Query: cre4f_name eq '[xl]Name'
- Add a step inside the apply to each - Condition
- Set the condition to be an expression. Check the length of the output to see if a record came back from the query. "List_records" below is the name of the previous step, with the space replaced by an underscore.
- Expression: length(outputs('List_records')?['body/value'])
- Add a step to the Yes condition.
- Update a record 2
- Set Item ID to Previous CDS entity (My table is called Member)
- Set the new values for the fields. I updated Email, first name, last name, phone.
- I had to use a formula to setup dates properly. "If the Join date field isn't empty, then set the join date to start of day(join date).
if(equals(empty(items('Apply_to_each')?['Join Date']),bool('True')),null,startOfDay(items('Apply_to_each')?['Join Date']))
Add another step to the no condition.
Create a new record. Entity is the CDS members.
Set the key.
Update all the fields as above.
Test. Fix. Repeat until it actually works.
