How to Bulk Delete Records from Dataverse with Power Automate

If you need to bulk delete data from a Dataverse table it is easy to setup a Bulk Record Deletion job from the Dynamics 365 Data Management portal. But if you are using Dataverse for Teams, there is no access to the portal.

In this post I am going to show you how to use the Dataverse Batch API to quickly and easily bulk delete records using Power Automate. This is a very similar flow to the method I showed on batch deleting items from SharePoint. This flow can be easily modified to batch create and update items. This will work for Full Dataverse and Dataverse for Teams environments.

Why use the Batch Method

Power Automate already makes it easy to delete items using the Delete a row action and it is simple to implement a flow that deletes records using an apply to each loop. An example might look like this:

The approach above is fine, if you do not have many records to delete, but it has two problems:

• The performance is bad. (Approximately 50 seconds to delete 100 rows)
• It will consume an API action for ever time the “Delete a row” action is executed.

Using the the batch API method takes just 5 seconds and uses only a single API action. The downside of the batch method is that it is more complex to implement, but I have made it as easy as possible for you to implement at your end.

The Batch method groups multiple operations into a single request. Here is an example of the HTTP Body of a batch operation that deletes 4 records:

--batch_40622dff-0fd4-4220-b7cd-59b5696d3a14
Content-Type: multipart/mixed; boundary=changeset_0c257ca5-afa7-4fac-8878-a358a39b8d48

--changeset_0c257ca5-afa7-4fac-8878-a358a39b8d48
Content-Type: application/http
Content-Transfer-Encoding: binary
Content-ID: 1

DELETE crb1c_sampletable(61def122-d743-ec11-8c62-0022481ac535) HTTP/1.1
Content-Type: application/json;type=entry

{}
--changeset_0c257ca5-afa7-4fac-8878-a358a39b8d48
Content-Type: application/http
Content-Transfer-Encoding: binary
Content-ID: 2

DELETE crb1c_monthlysalesanalysises(62def122-d743-ec11-8c62-0022481ac535) HTTP/1.1
Content-Type: application/json;type=entry

{}
--changeset_0c257ca5-afa7-4fac-8878-a358a39b8d48
Content-Type: application/http
Content-Transfer-Encoding: binary
Content-ID: 3

DELETE crb1c_monthlysalesanalysises(63def122-d743-ec11-8c62-0022481ac535) HTTP/1.1
Content-Type: application/json;type=entry

{}
--changeset_0c257ca5-afa7-4fac-8878-a358a39b8d48
Content-Type: application/http
Content-Transfer-Encoding: binary
Content-ID: 4

DELETE crb1c_monthlysalesanalysises(64def122-d743-ec11-8c62-0022481ac535) HTTP/1.1
Content-Type: application/json;type=entry

{}
--changeset_0c257ca5-afa7-4fac-8878-a358a39b8d48--
--batch_40622dff-0fd4-4220-b7cd-59b5696d3a14--

Azure Application Registration

Before you can use the batch API method you need to register an App in Azure to be able to access it. Here is how to do it:

• Navigate to the Azure App Registration Portal
• Click on New Registration
  
• Give the App Registration a meaningful name.
• Choose Accounts in this organisational directory only
• Click Register.

Next do the following

• Click on API Permissions,
• Choose Add a permission,
• Select APIs my organisation uses in the search box, and search for Dataverse.
• Select the Dataverse option that becomes available (Application ID 00000007-0000-0000-c000-000000000000)
• On the permissions pane, check the box for user_impersonation and then click Add permissions

It should look like this:

On the API permissions page, click the link to Grant admin consent for organisation name:

Next go into authentication and tick the check box for Access tokens (used for implicit flows) and set Allow public client flows to yes:

Collect required information

Before you can run the batch delete flow, you need to collect these four pieces of information:

• Environment URL of the Teams or Dynamics Environment.
• Tennant ID of the Office 365 environment.
• Application ID of the newly created Azure App Registration.
• An Office 365 Username and Password with permissions to read/write to the Dataverse tables.

Get the Organisation Environment URL

The procedure to get the Environment URL is different if you are using Dataverse for Teams or full Dataverse.

Full Dataverse Environment

For a full environment simply go to the Power Platform Admin Centre and select your environment. The Environment URL will be shown:

Dataverse for Teams Environment

If you are working with a Dataverse for Teams environment, go to the PowerApps tab in Teams and then click About, from there click on Session details:

Click on the Copy details button and take the value from the Instance URL.

Office 365 Tennant and Application ID

Next you need your Office 365 Tennant ID and application ID, these are both easy to find on the Azure App Registrations page:

Implement the Power Automate Flow

Now the app is registered and you have collected all of the required information you are ready to implement the flow. I have created a scope so you can simply copy and paste the entire scope into your flow.

The flow looks like this:

In order to implement this flow yourself, you need to do the following:

• Add an Initialize variable action and create a variable called itemCount with an initial value of -1
• Copy the scope code below, and paste it into your flow.
• Modify the settings compose action to match your environment.
• Change the List Rows action to the table you want to delete records from (add any oData filters as required).

Batch Delete Scope Code

Here is the code for you to copy and paste into your Flow:

{
	"id": "f090f56a-c47b-453e-bf00-1434-d02958e2",
	"brandColor": "#8C3900",
	"connectionReferences": {
		"shared_commondataserviceforapps": {
			"connection": {
				"id": ""
			}
		}
	},
	"connectorDisplayName": "Control",
	"icon": "data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=",
	"isTrigger": false,
	"operationName": "Dataverse_Batch_Delete",
	"operationDefinition": {
		"type": "Scope",
		"actions": {
			"settings": {
				"type": "Compose",
				"inputs": {
					"org": "exampleOrg.crm11.dynamics.com",
					"tennantID": "11111111-1111-1111-1111-111111111111",
					"applicationID": "22222222-3333-4444-5555-666666666666",
					"userName": "some.user@somedomain.com",
					"Password": "userPassword"
				},
				"runAfter": {},
				"description": "Settings for the Batch Delete Scope",
				"trackedProperties": {
					"batchGUID": "@{guid()}",
					"changeSetGUID": "@{guid()}"
				},
				"metadata": {
					"operationMetadataId": "9070737a-20ad-44ee-995b-9a9c71c1883d"
				}
			},
			"HTTPAuth": {
				"type": "Http",
				"inputs": {
					"method": "POST",
					"uri": "https://login.microsoftonline.com/@{outputs('settings')['tennantID']}/oauth2/v2.0/token",
					"headers": {
						"Content-Type": "application/x-www-form-urlencoded"
					},
					"body": "client_id=@{outputs('settings')['applicationID']}&scope=https://@{outputs('settings')['org']}//user_impersonation &username=@{outputs('settings')['userName']}&password=@{outputs('settings')['Password']}&grant_type=password"
				},
				"runAfter": {
					"settings": ["Succeeded"]
				},
				"description": "Authenticate with the Azure App Registration",
				"metadata": {
					"operationMetadataId": "af21daac-52b9-48e6-8dff-be4d7550cc6f"
				}
			},
			"Template": {
				"type": "Compose",
				"inputs": "--changeset_@{actions('settings')?['trackedProperties']['changeSetGUID']}\nContent-Type: application/http\nContent-Transfer-Encoding: binary\nContent-ID: |ID|\n\nDELETE |editLink| HTTP/1.1\nContent-Type: application/json;type=entry\n\n{}",
				"runAfter": {
					"HTTPAuth": ["Succeeded"]
				},
				"description": "Template for the batch deletion",
				"metadata": {
					"operationMetadataId": "a543aa01-3229-4ac8-be20-3006632d00bc"
				}
			},
			"Do_until": {
				"type": "Until",
				"expression": "@equals(variables('itemCount'), 0)",
				"limit": {
					"count": 60,
					"timeout": "PT1H"
				},
				"actions": {
					"List_rows": {
						"type": "OpenApiConnection",
						"inputs": {
							"host": {
								"connectionName": "shared_commondataserviceforapps",
								"operationId": "ListRecords",
								"apiId": "/providers/Microsoft.PowerApps/apis/shared_commondataserviceforapps"
							},
							"parameters": {
								"entityName": "",
								"$top": 1000
							},
							"authentication": {
								"type": "Raw",
								"value": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"
							}
						},
						"runAfter": {},
						"description": "The table to delete from (In batches of 1,000)",
						"metadata": {
							"operationMetadataId": "564b0815-e768-4ef8-b625-32015acb8e22"
						}
					},
					"Select": {
						"type": "Select",
						"inputs": {
							"from": "@range(0,length(outputs('List_rows')?['body/value']))",
							"select": "@replace(\r\n\treplace\r\n\t(\r\n\t  outputs('Template'), '|editLink|', \r\n\t  outputs('List_rows')?['body']['value'][item()]?['@odata.editLink']\r\n\t),\r\n\t'|ID|',\r\n\tstring(add(item(),1))\r\n)"
						},
						"runAfter": {
							"Set_variable": ["Succeeded"]
						},
						"metadata": {
							"operationMetadataId": "d8cb63cb-caf5-4606-9b72-549556a3585d"
						}
					},
					"Send_Batch": {
						"type": "Http",
						"inputs": {
							"method": "POST",
							"uri": "https://@{outputs('settings')['org']}/api/data/v9.0/$batch",
							"headers": {
								"authority": "@{outputs('settings')['org']}",
								"accept": "application/json",
								"Content-Type": "multipart/mixed;boundary=batch_@{actions('settings')?['trackedProperties']['batchGUID']}",
								"authorization": "Bearer @{body('HTTPAuth')['access_token']}",
								"OData-Version": "4.0",
								"OData-MaxVersion": "4.0"
							},
							"body": "--batch_@{actions('settings')?['trackedProperties']['batchGUID']}\nContent-Type: multipart/mixed; boundary=changeset_@{actions('settings')?['trackedProperties']['changeSetGUID']}\n\n@{join(body('Select'), decodeUriComponent('%0A'))}\n--changeset_@{actions('settings')?['trackedProperties']['changeSetGUID']}--\n--batch_@{actions('settings')?['trackedProperties']['batchGUID']}--"
						},
						"runAfter": {
							"Select": ["Succeeded"]
						},
						"metadata": {
							"operationMetadataId": "f87a2569-7b77-4cd0-a06e-a5dcde1e5a7d"
						}
					},
					"Set_variable": {
						"type": "SetVariable",
						"inputs": {
							"name": "itemCount",
							"value": "@length(outputs('List_rows')?['body/value'])"
						},
						"runAfter": {
							"List_rows": ["Succeeded"]
						},
						"metadata": {
							"operationMetadataId": "e56f6642-f33d-4af8-bcf3-f793b935c6f5"
						}
					}
				},
				"runAfter": {
					"Template": ["Succeeded"]
				},
				"description": "Loop until no more rows remain",
				"metadata": {
					"operationMetadataId": "43ddd2a4-7b8c-49ad-a273-f1d09fdb12e8"
				}
			}
		},
		"runAfter": {
			"Initialize_variable": ["Succeeded"]
		},
		"metadata": {
			"operationMetadataId": "2a303e21-8ffe-48a4-87a5-1abce1a73989"
		}
	}
}

To add this code to your flow, simply copy all of the code above. Go to your flow, create a new step and then go to My Clipboard, press CTRL-V and then you will see the scope appear in your clipboard. Simply click the action to add it to your flow.

Configure the Settings Action

The settings action contains everything required to run the batch delete operation:

{
  "org": "exampleOrg.crm11.dynamics.com",
  "tennantID": "11111111-1111-1111-1111-111111111111",
  "applicationID": "22222222-3333-4444-5555-666666666666",
  "userName": "some.user@somedomain.com",
  "Password": "userPassword"
}

Change the contents of the action to match the values that you collected earlier in this post.

You will notice in this flow I have stored the credentials in the settings action. In order to make this more secure I would suggest that you follow the instructions on Hiding passwords in Power Automate from Matthew Devaney.

Configure the List Rows action.

Finally, reconfigure the List Rows action:

• Select the table that you want to delete records from.
• Add any filters that you want applied.

Note: Do not set row count for the list rows action to be greater than 1,000. The batch API can contain a maximum of 1,000 induvial requests.

Conclusion

The batch method demonstrated in this flow makes bulk delete operations in Dataverse much quicker than using individual requests and it is easy to implement. I am going to follow this post up with an example on how to batch create items as well.