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.
Table of contents
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--
Warning!!
Please be careful when implementing this flow.
You could easily mass delete data from your environment!
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": "[email protected]", "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": "[email protected]", "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.
Ha says
Thank you so much for this explanations!
Can you tell us how do you have the entire code of your flow please?
Paulie says
Just copy the code from the section titled “Batch Delete Scope Code” and then go into a flow and create a new action. Go to “My Clipboard”, press CTRL-V and then you will see the scope appear.
ha says
Yes, i know!
But i m talking about another flow what i created, what can i have the script like here?
Paulie says
I’m not sure if I know what you mean. But you can put your actions into a scope and then just copy the scope. Once you have copied it paste it into a text editor.
ha says
Okey!
It’s clear for me!
Thank you again!
BTW, the flow is very useful!
Tyler says
Thanks Paul!
After the SharePoint batch set-ups I was hoping you would start doing some for Dataverse. I’ll have to play around with this a little.
devman2k says
Anyone get this working with MFA ?
Clayce says
Hi Paul,
Can you do this same process with a Service Principle, we like to use it for high API call processes. If so, can you explain what would be different? Thanks- Clayce
Jonathan Riches says
Hi
Am I missing something, is there any advantage to using this method versus the Power Automate Dataverse Unbound Operation Bulk Delete, or is it just that this post pre-dates its availability?
e.g. https://www.philcole.org/post/bulkdelete/
Paulie says
Yes, there are some limitations on that action (but I can’t actually remember what they are now!).
I should have written it into the post when I wrote it!