How to use Power Automate to Import & Deduplicate data from Excel Online to the Dataverse

 We know that importing contact records from an Excel file to the Dataverse is fairly easy, there are several tools available, however, have you tried to import records from a Excel Online file using Flow? I recently needed to do this and to my surprise, it was not as simple as I originally thought.

In this post we will review some of tools and functions I used to create the final solution.

• How to use the Power Automate Search records action (Currently in preview)
• How to use the empty() expression
• How to use a condition to check if an array is empty
• How to import records from an Excel Online file using Power Automate

Recently, I needed to import contact records that were in a OneDrive folder, however, about 50% of the contacts had already been incorrectly uploaded, so I need to not only import the records but check for duplicates and fix the existing records.

In short, I needed to create a Flow that will search for a contact record in the Dataverse using an email address, if the record is found, clear out the first name, update the last name and set the contact type. If the contact was not found, then create the contact record.

How hard can this possibly be? Well, it was not as straight forward as I thought.

How to deduplicate and import records from Excel Online using Power Automate (Flow)

Before we jump right into how to create the Flow, lets evaluate the requirements.

Current Application

• Dynamics 365 Marketing

Requirements

• Import a list of contacts from an Excel Online file
• 50% of the contacts have already been imported but in error, their first name and email address is the same value, this must be corrected.
• For any contacts that have not be imported, import them.
• Update all the contacts to have a contact type of Account.
• Create a segment in Dynamics 365 Marketing, for all contacts of type Account.

Some challenges I faced while creating the flow

• In order to use the Get row by Id action, I needed the Dataverse record ID, so that rules out using this action.

• I tried using the List Rows action and filtered with the email address from the Excel file using FetchXML but I since you cannot access the dynamic values in the filter, that did not work. I know, sounds weird, but unless there is a trigger or an action before the list record step, their Dataverse columns are not available. I was using a PowerApps Trigger.
• Since I could not find the Rows using the Get Row by ID, I had a look at what other options were available, and to my surprise, there is a new action called Search rows (preview). Fantastic, just when I needed it.
• Final challenge I faced was, trying to use a condition to determine if any records were found from the search. When a record is found, the output is an Array of Objects otherwise an empty array. So, you would think that I could just test the length of the array using the expression length(array) = 0 to determine which records are found and not, well Flow does not allow this.

So, here is how to configure the Flow to deduplicate, import and update records in one go.

To start, create a blank Instant Flow

Add the PowerApps trigger since this Flow will run on demand

Add a PowerApps trigger

Next, add the List rows present in table action, and set the location of the file as per your file configuration. (Your data must be in a table format).

This action will retrieve the records from the Excel Online file

Add an Apply to each control and set the first parameter as the output value from the list rows action

The Apply to each control is a loop that will process each of the records in the Excel file

Next, add the Search rows action and set the search item to the dynamic value of the email address in the excel file, then set the table filter item to the logical name for the contact table (contact).

The Search rows action uses the email address from the Excel file to find a matching record in the Dataverse.

Next, you should add a condition control, this will be to test the result of the search to determine if a record was found with the matching email address.

The condition will test if a record is found. If no record is found an empty array is returned. empty(outputs(‘Search_rows_(preview)’)?[‘body/value’])

Next, on the if yes branch, add the Dataverse Add a new row action and map the fields from the Excel file to the Dataverse columns.

Map the Excel data columns to the Dataverse columns.

On the If no branch, add an apply to each action and set the first parameter to the output of the List rows.

If the record is found, you will retrieve the record and then update it with the values from the Excel file.

Finally, add a Dataverse Update a row action and map the columns you would like to update

Map the Excel columns to the Dataverse columns you would like to update. To clear out any values, use the null expression

There you have it, the Flow will retrieve the contacts from the Excel Online file, search for them in the Dataverse using the email address, if the record is not found, it will be created, otherwise, the record is updated as needed.

Once you have complete configuring the Flow, it should look like this.

I hope you found this article helpful.