Quickly & efficiently batch update & batch create (upsert) tens of thousands of records in Dataverse. Creates and/or updates 1000 records in Dataverse for every 6 actions and without the need for an Azure Application set-up.
Set-Up
Go to the bottom of this post & download the BatchDataverse_1_0_0_xx.zip file. Go to the Power Apps home page (https://make.powerapps.com/). Select Solutions on the left-side menu, select Import solution, Browse your files & select the BatchDataverse_1_0_0_xx.zip file you just downloaded. Then select Next & follow the menu prompts to apply or create the required connections for the solution flows.
You should get to a screen where you are prompted to create a new Invoke HTTP connection. Click to the right of the connection & create a new connection to open a menu like this.
In a different tab, go to any Dataverse table, click Tools, & in the dropdown select API link to table to open a page that will contain the dynamics url you will need for the Invoke HTTP connection. Copy the dynamics url & paste it into both the Base Resource & Microsoft Entra inputs. Then sign in for the connection & complete the import.
Once imported, find the Batch Dataverse solution in the list of solution & click it to open the solution. Then click on the dots next to the Dataverse Batch Upsert item & select Edit to open the flow.
Once inside the flow, you will need to change the TableLogicalName in the settings action to the PLURAL logical name for the table you want to batch create on. Also change DestinationTableGuidColumnName to the logical name of the main Dataverse guid key for your destination table (should be the non-plural logical name for the table followed by id), change DestinationTableKeyColumnName inputs to the key column(s) in Dataverse you want to compare with your source dataset keys to determine what records to update, and change the & SourceKeyColumnName inputs the key column(s) in your source dataset you want to compare with your destination Dataverse table keys to determine what records to update.
Choose the Dataverse table you are updating in the List rows Destination table action.
You will also need to change the source for the From of the GenerateDataverseData action to your chosen source data-set, change the left-side column names to match your table columns' logical names, & change the right-side values to the values from your chosen source dataset. But DO NOT edit or remove the GuidKey line.
You can find the Table logical name by going to the table menu in dataverse, selecting Properties, selecting Advanced options, & then copying the Logical name. Make sure to add an s to the end of the table name if it doesn't already have one.
You can find the column logical names by going to the table menu, going to Columns, then selecting the 3 dots for the given column, selecting Advanced options in the pop-up, & copying the Logical name there. These should not require adding a plural s.
And note to add values for things like Choice columns, you may need to find the integers used to represent each choice option & use a conditional expression to assign those integers based on the source data-set values coming in.
The Batch Upsert flow includes a batch update & a batch create. The screenshots above include what changes are needed to set up & run that upsert flow.
The other Batch Full Sync flow has the same batch update & batch create as the upsert flow, but also includes a batch delete scope where one can input the dynamic content output for one’s source data action in the 1st Select Keys array from source action so it can determine what items exist in the Dataverse table that do not exist in the updated source data & then delete them.
Thanks for any feedback!
Reach out on LinkedIn (https://www.linkedin.com/in/kolota/) if you want to hire me to consult on or build more custom Microsoft solutions for you.
And please subscribe to my YouTube channel (https://youtube.com/@tylerkolota?si=uEGKko1U8D29CJ86).
Placeholder1
Placeholder 2
Dataverse Batch Create Version 0
Copy & paste the below code into the My clipboard section of the new action menu in the classic flow designer
{"id":"cf81a667-60a3-42b2-8a33-a0bd1fb7634f","brandColor":"#8C3900","connectionReferences":{"shared_webcontents":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_webcontents/connections/shared-webcontents-07bbfcc1-2dce-4866-8243-dcb9ed7d55ed"}}},"connectorDisplayName":"Control","icon":"data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"Dataverse_Batch_Create","operationDefinition":{"type":"Scope","actions":{"settings":{"type":"Compose","inputs":{"TableLogicalName":"new_testtables","BatchSize":800},"runAfter":{},"description":"Settings for the batch actions. MUST USE THE PLURAL OF THE LOGICAL TABLE NAME.","trackedProperties":{"batchGUID":"@{guid()}","changeSetGUID":"@{guid()}"},"metadata":{"operationMetadataId":"9070737a-20ad-44ee-995b-9a9c71c1883d"}},"SampleData":{"type":"Compose","inputs":[{"name":"String0","date":"@{utcnow()}","number":"123","status":"Complete"},{"name":"String1","date":"@{utcnow()}","number":"456","status":"Complete"},{"name":"String2","date":"@{utcnow()}","number":"789","status":"Complete"}],"runAfter":{"settings":["Succeeded"]},"metadata":{"operationMetadataId":"4e6fb468-91d1-4ea5-8e4a-b2f0910c595e"}},"GenerateDataverseData":{"type":"Select","inputs":{"from":"@outputs('SampleData')","select":{"new_name":"@item()?['name']","new_date":"@item()?['date']","new_number":"@item()?['number']","new_progressstatus":"@if(equals(item()?['status'], 'Pending'), 100000000, if(equals(item()?['status'], 'In Progress'), 100000001, 100000002))"}},"runAfter":{"SampleData":["Succeeded"]},"description":"Table column logical names on the left & new values on the right.","metadata":{"operationMetadataId":"f4b50f35-3d61-4ed3-8b13-b77d4f46d078"}},"Template":{"type":"Compose","inputs":"--changeset_@{actions('settings')?['trackedProperties']['changeSetGUID']}\nContent-Type: application/http\nContent-Transfer-Encoding: binary\nContent-ID: |ID|\n\nPOST /api/data/v9.2/@{outputs('settings')['TableLogicalName']} HTTP/1.1\nContent-Type: application/json;type=entry\n\n|RowData|\n","runAfter":{"GenerateDataverseData":["Succeeded"]},"metadata":{"operationMetadataId":"6d8a7a3b-2002-428c-b686-dffa33e63b32"}},"Apply_to_each":{"type":"Foreach","foreach":"@chunk(body('GenerateDataverseData'), outputs('settings')['BatchSize'])","actions":{"Select_2":{"type":"Select","inputs":{"from":"@range(0, length(items('Apply_to_each')))","select":"@replace(replace(outputs('Template'), '|RowData|', string(items('Apply_to_each')[item()])), '|ID|', string(add(item(), 1)))"},"runAfter":{},"metadata":{"operationMetadataId":"a137eeeb-941c-4d75-9d06-84eaf76bee86"}},"SendBatch_2":{"type":"OpenApiConnection","inputs":{"host":{"connectionName":"shared_webcontents","operationId":"InvokeHttp","apiId":"/providers/Microsoft.PowerApps/apis/shared_webcontents"},"parameters":{"request/method":"POST","request/url":"/api/data/v9.2/$batch","request/headers":{"OData-MaxVersion":"4.0","OData-Version":"4.0","If-None-Match":"null","Accept":"application/json","Content-Type":"multipart/mixed;boundary=batch_@{actions('settings')?['trackedProperties']['batchGUID']}"},"request/body":"--batch_@{actions('settings')?['trackedProperties']['batchGUID']}\nContent-Type: multipart/mixed; boundary=changeset_@{actions('settings')?['trackedProperties']['changeSetGUID']}\n\n@{join(body('Select_2'), decodeUriComponent('%0A'))}\n--changeset_@{actions('settings')?['trackedProperties']['changeSetGUID']}--\n--batch_@{actions('settings')?['trackedProperties']['batchGUID']}--"},"authentication":{"type":"Raw","value":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"}},"runAfter":{"Select_2":["Succeeded"]},"metadata":{"operationMetadataId":"ce66789c-1571-450c-b6a1-bfdd03e88866"}},"Results_2":{"type":"Compose","inputs":"@base64ToString(body('SendBatch_2')['$content'])","runAfter":{"SendBatch_2":["Succeeded"]},"metadata":{"operationMetadataId":"97211a81-04c9-4b3f-b039-6bf962cbc1c3"}}},"runAfter":{"Template":["Succeeded"]},"metadata":{"operationMetadataId":"6cc19636-6289-44a4-a3d0-25d03e29f3a6"}}},"runAfter":{}}}