Merging Dataverse Records with Power Automate

Duplicate data is an issue many of us are familiar with. Dynamics does have methods to detect duplicates as they are being created and ways to use rules to gather potential duplicates after the fact. However, someone still needs to go through that list and determine if they should be merged one by one. Now if you have thousands of duplicates to go through that can make for a time-consuming task. So, how do we list duplicates and merge them in one process? One solution would be to use Power Automate.

What is Power Automate?

Users can create automations between services and applications with Microsoft Power Automate. Users can create automated processes that replace time-consuming or repetitive activities. Specific events can be set at a trigger for these types of automation.

Setting up the Flow

Before we get started on the flow, we are going to need a way to let us know when a record has been merged so we only run the process once per set of duplicates. We can solve this by creating a yes/no column on the table we are merging. Now that we have that column we can start on the flow. The first thing we need is a list step that lists all records that we are interested in merging. We can use an Odata filter or FetchXML. Keep in mind the way we sort this list step will determine the Primary record for each set later on.

Next, we need to use an apply to each step for each record in the list. The first thing we’ll need to do here is create a get record step to grab a fresh copy of the record we are on to see if the merged column is set to yes or no. If it’s set to no, we can go ahead and end this loop. If it’s set to yes, we will want another list step that lists all records that match the record in the initial loop. The logic here would be similar to how duplicate detection rules are set up out of the box.

Merging Records

Now we are at the merge portion of the flow. First, we’ll want to create an apply to each loop that runs on each duplicate we found earlier. Then, we’re going to want to create a Get Target step that will grab a fresh copy of the primary record to use in the merge action. This way we can ensure that any data copied from any earlier merge is preserved.

To merge the records, we can use an unbound action. Once the step is created, we can select Merge as the Action Name. Next, we will be presented with a section for Target, Subordinate, UpdateContent, and PerformParentingCheck. The main downside to this method is that we don’t have the option to “Merge records by choosing fields with data” like we do when merging records within a model-driven app. This just means we need to determine the logic for each column. Fortunately, child records will be reassigned to the target record automatically.
Below is how the Target and Subordinate sections will need to be populated. The target record is the one that will remain active at the end.

The UpdateContent section will look similar to this. With a line for each field column we are including in the merge. The expression will look at the Target record to see if it has data. If it does, we are going to keep it. If not, we will take the data from the Subordinate record.

Expression:

 if(equals(outputs(‘Get_Target'[‘body/emailaddress1’],null),items(‘Apply_to_each’)?[’emailaddress1′],outputs(‘Get_Target’)?[‘body/emailaddress1’])

Finally, we can end the flow by marking each record as merged with the column we created earlier.

Reach out to Beringer today!

We love to implement Microsoft Dynamics 365 and Power Platform solutions here at Beringer.  We’ve been working with Microsoft Dynamics since its inception, and we’re always finding innovative ways to implement the latest tools and help automate business processes.

Beringer Technology Group, a leading Microsoft Partner specializing in Microsoft Dynamics 365 and CRM for Distribution also provides expert Managed IT Services, Backup and Disaster Recovery, Cloud Based Computing, Email Security Implementation and Training,  Unified Communication Solutions, and Cybersecurity Risk Assessment.