Sometimes the standard import tool in Dynamics 365 CRM is all you need, especially when the data in your import file maps directly to CRM fields. Other imports require additional processing before the data can be added.

For example, you may want to combine information from multiple columns or bring together information from different data sources. In this example, we’ll use an Excel file containing information that we want to add to existing CRM contacts. Although we’re using Excel and Dynamics 365 CRM, the general process can also apply to other data sources and destinations.

Set up the Power Automate flow

Start by creating a flow in Power Automate and making sure you are working in the correct environment for your CRM database. This example is designed as a single-run process, so we’ll use a manual trigger rather than an automated trigger.

Next, add the Excel action List rows present in a table to retrieve the data. When using Excel this way, the data must be formatted as a table within the spreadsheet. This example uses a file stored in OneDrive, but the source file could also be stored in SharePoint. Once you select the Excel file, choose the appropriate table. If your spreadsheet contains multiple tables, make sure you select the correct one.

Track processed rows

For larger imports, consider adding columns to the spreadsheet that let the flow write information back to each row. In this example, a Processed column allows us to identify whether a row has already gone through the flow. Later, we’ll also track whether a corresponding CRM contact was found. This makes it easier to identify records that successfully ran through the process and records that may require additional attention. You can also use the Filter Query option in the Excel action to ignore rows that have already been processed.

Retrieve larger Excel datasets

By default, the Excel List rows action retrieves up to 256 records. For larger Excel files, open the action's settings, enable pagination, and enter a custom threshold. The threshold can be set as high as 100,000 records. Once the Excel data has been retrieved, you can begin processing each row.

Find the existing CRM contact

Our Excel file does not contain the CRM contact ID, so we need to locate the appropriate contact using information that we do have. Add a Dataverse List rows action and select the Contacts table. In this example, the spreadsheet contains an email address, so we use that to search CRM. The filter uses the logical CRM field name emailaddress1 and the email address from the current Excel row. Because we only need one matching contact, limit the result to one row. We also only need the contact's ID for the next step, so enter contactid in the Select columns field.

Check whether a contact was found

Next, add a condition that determines whether the contact search returned a record. Use the length() function on the values returned by the List rows action. If the length is greater than zero, a matching contact was found. Otherwise, the flow follows the false path. If no contact is found, add an Excel Update a row action. Use Email as the key column and the email address from the current row as the key value. Then mark:

• Processed: Yes
• Contact Found: No

This tells us that the row successfully went through the flow but did not match an existing contact. Depending on your requirements and the information available in the import file, you could instead create a new CRM record. In this example, we’ll simply record that no contact was found.

Update the matching CRM contact

When a contact is found, use a Dataverse Update a row action. Select the Contacts table and use the contact ID returned by the earlier search to identify the record. Although the contact search is limited to one result, List rows still returns an array. Because of this, Power Automate places the update action inside another Apply to each loop. In this example, only one contact is expected, so the loop will not update multiple contacts.

Next, select the CRM fields you want to update. For this example, we update the contact's State/Province field using the state value from the Excel file. If the import requires transformations, translations, or combinations of data, those changes can be performed after retrieving the source data and before updating the CRM record.

Mark successful updates in Excel

After updating the CRM contact, add another Excel Update a row action. Again, use Email as the key column and the current row's email address as the key value. This time, set:

• Processed: Yes
• Contact Found: Yes

After the flow finishes, these tracking columns make it easier to review the results. For example, you can identify rows where processing did not complete or filter for records where a corresponding contact was not found.

Be careful with variables inside loops

This example passes returned values directly between actions rather than storing them in variables. Variables can require additional consideration inside Apply to each loops when multiple iterations run in parallel. One loop iteration could change a variable before another iteration reaches the action that uses it, potentially resulting in incorrect data. You can limit the degree of parallelism to one to avoid that issue, but doing so can result in a longer flow run when processing many thousands of rows.

Variables may still make sense when your flow needs to perform several translations or modifications before updating the destination data. If you use them this way, keep concurrency in mind. Another option is to store the contact ID in a variable inside the loop and then place the CRM update outside the additional Apply to each loop.

Consider recurring runs for large updates

If your data source limits how many records can be retrieved at once, you can also process the bulk update across recurring flow runs. This approach works particularly well when you can write back to the source file and mark each completed row as processed.

After seeing how long one batch takes, you can change the flow to use a recurrence trigger and leave enough time between runs to prevent them from overlapping. You can also add a condition after retrieving the source records to determine whether any unprocessed records remain. When no records are returned, the update is complete. The flow can then send an email notification indicating that the recurring flow can be turned off.

Putting it all together

A basic Power Automate bulk-update process can:

• Retrieve source data from an Excel table.
• Filter out previously processed rows.
• Search Dynamics 365 CRM for matching records.
• Determine whether each record exists.
• Update matching CRM records.
• Mark processed rows and unmatched records in Excel.
• Transform or combine data before performing an update when needed.
• Use pagination or recurring runs to accommodate larger datasets.

Although this example uses an Excel file stored in OneDrive to update contacts in Dynamics 365 CRM, the same general approach can be adapted for data stored in SharePoint or other sources and adjusted based on the requirements of your bulk-update process.