• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Tachytelic.net

  • Get in Touch
  • About Me

Power Platform

How to merge arrays in Power Automate

July 12, 2022 by Paulie 2 Comments

This post describes how to merge two separate arrays that contain a common value. My friend Tom Riha recently wrote a good post on how to do this. His method will work well for a small dataset, but for a large array it would be quite time consuming as it uses an Apply to each loop.

I also did a video on this subject in response to the a question on the Power Automate Forums:

The Example Data

We are going to combine two arrays, here is the first one:

[
    {
        "Name": "Tom",
        "Country": "CZ"
    },
    {
        "Name": "Paul",
        "Country": "UK"
    }
]

The second array:

[
    {
        "Name": "Tom",
        "Age": "younger than Paul"
    },
    {
        "Name": "Paul",
        "Age": "older than Tom"
    }
]

The combined array we want to create:

[
  {
    "Name": "Tom",
    "Country": "CZ",
    "Age": "younger than Paul"
  },
  {
    "Name": "Paul",
    "Country": "UK",
    "Age": "older than Tom"
  }
]

In order to combine these into a single array, we need a common value in each array. In this example the Name key is common to both arrays. We are going to use the powerful xpath expression to perform this in a single step.

Array Preparation

Before we can use xpath, the second array needs to be converted to XML, and before we can do that, we need to do some preparation. Check out this screenshot:

Power Automate Flow preparing an array to be merged

All of the above actions are Compose actions. The step named PrpareArray2 simply wraps the output of Array2 around some additional JSON and produces the following output:

{
  "Root": {
    "Array": [
      {
        "Name": "Tom",
        "Age": "younger than Paul"
      },
      {
        "Name": "Paul",
        "Age": "older than Tom"
      }
    ]
  }
}

Now that it is in the proper format the step named XML converts the array to XML and produces the following output:

<Root>
	<Array>
		<Name>Tom</Name>
		<Age>younger than Paul</Age>
	</Array>
	<Array>
		<Name>Paul</Name>
		<Age>older than Tom</Age>
	</Array>
</Root>

Combine the Arrays

With all of that prep work done, we can begin to actually merge the arrays. See this Select action:

Power Automate Selection action which combines two separate arrays.

As you can see, all of the work is done in a single select action, but it requires a bit of explanation. The first part is straightforward:

  • The input for the select is Array1
  • The Keys Name and Country are drawn from Array1 using the item() syntax.
  • The Age key is pulled from the XML using the xpath expression.

The xpath expression is easier to read like this:

xpath
(
  outputs('XML'), 
  concat('//Array[Name/text()="', item()['Name'], '"]/Age/text()')
)?[0]

The concat function is used to dynamically create an xpath query by using the name from the current item. So in the first iteration of the loop the xpath expression would be:

//Array[Name/text()="Tom"]/Age/text()

What are we asking xpath to do here:

  • Find all of the elements within Array
  • Only find nodes within Array where Name is equal to Tom
  • Grab the text value of the Age where the Name matched.

You can test this out for yourself by copying the XML above and pasting it into xpather.com – then copy the expression above and you should see results like this:.

Image of results from xpath query on xpather.com

xpath will return an array of results, even if there is only a single match, so the final part of the expression is: [0] to grab the first result of the array.

So in a single select action we can combine the arrays easily. In our example we could simply it further by using the addProperty function in the select instead of key/value pairs.

An alternative select action which produces the same output in a simpler action.

In this example the Select action was changed to text mode instead key/value mode. Then I used the expression:

addProperty
(
  item(), 
  'Age', 
  xpath(outputs('XML'), concat('//Array[Name/text()="', item()['Name'], '"]/Age/text()'))?[0]
)

This simply takes the current item and adds a new property called Age and assigns the value returned from the xpath expression.

Conclusion

Which method should you use and is this method worth the hassle?

It depends on how performance dependant your flow is. If you are only working with a small amount of data, the method Tom shows is easier and will execute quickly.

If you are working with hundreds or thousands of rows of data, then this method will be much faster. It’s always fun to compare different methods of doing the same thing!

Filed Under: Power Platform Tagged With: Power Automate

Sort an Array of Objects with Power Automate

July 4, 2022 by Paulie Leave a Comment

This post explains how to sort an array of objects in Power Automate. This method only works for objects that contain a numeric value on which to sort.

I have done a previous post on How to Sort an Array in Power Automate using Office Scripts, which works very well, but Office Scripts are not available in all environments.

This example demonstrates how to sort an array of objects using only native Power Automate actions. The question was originally raised on the Power Automate community forums. The original poster actually wants to find only the top three values, which I will also cover.

Sorting in Power Automate

Since there is no method to sort an array, we have to build our own for this purpose, which is surprisingly difficult to do. Here is the sample JSON array which we are going to sort:

[
  {
    "label": "Car",
    "score": 0.26
  },
  {
    "label": "Bus",
    "score": 0.26
  },
  {
    "label": "Train",
    "score": 0.34
  },
  {
    "label": "Boat",
    "score": 0.45
  },
  {
    "label": "Truck",
    "score": 0.62
  },
  {
    "label": "Bike",
    "score": 0.11
  }
]

Notice that both Car, and Bus have the same score. The aim is to sort the array in either descending or ascending order to look like this:

[
  {
    "label": "Truck",
    "score": 0.62
  },
  {
    "label": "Boat",
    "score": 0.45
  },
  {
    "label": "Train",
    "score": 0.34
  },
  {
    "label": "Bus",
    "score": 0.26
  },
  {
    "label": "Car",
    "score": 0.26
  },
  {
    "label": "Bike",
    "score": 0.11
  }
]

Power Automate does have the max and min expressions which can help you find the largest and smallest entries of an array, but there is no way to determine the position of the element within the array.

The xpath expression however, can be used to find the position of an array element, so we can use that to created a sorted array.

Here is an example flow, which I will explain further below:

To implement this flow for yourself, you need to Initialize two variables. One called ArrayOfObjects which contains the array you want to sort and a second one called Sorted – which should be empty.

The flow works by first finding the position of the highest or lowest values from ArrayOfObjects and then:

  • Inserting each value into the new array.
  • Removing the found value from the original array.

You can copy the scope and paste the entire thing into your own flow by copying the code below:

{
  "id": "4c808717-bb30-4633-94fa-2af031ee4cdc",
  "brandColor": "#8C3900",
  "connectionReferences": {},
  "connectorDisplayName": "Control",
  "icon": "data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=",
  "isTrigger": false,
  "operationName": "SortArray",
  "operationDefinition": {
    "type": "Scope",
    "actions": {
      "Settings": {
        "type": "Compose",
        "inputs": {
          "SortOrder": "Dsc",
          "Attribute": "score"
        },
        "runAfter": {},
        "description": "Modify the settings below to change the sort order or the object attribute to sort on",
        "metadata": {
          "operationMetadataId": "5e788764-ea85-4254-b18d-6fe0e9e4561a"
        }
      },
      "Apply_to_each": {
        "type": "Foreach",
        "foreach": "@range(0,length(variables('ArrayOfObjects')))",
        "actions": {
          "RemoveFoundElement": {
            "type": "Compose",
            "inputs": "@union\r\n(\r\n  take(variables('ArrayOfObjects'),outputs('ElementPosition')),\r\n  skip(variables('ArrayOfObjects'),add(outputs('ElementPosition'),1))\r\n)",
            "runAfter": {
              "Append_to_Sorted_Array": [
                "Succeeded"
              ]
            },
            "metadata": {
              "operationMetadataId": "6f13a269-e383-48bb-9899-7b3fb1243e80"
            }
          },
          "Update_Array_Of_Objects_With_Removed_Element": {
            "type": "SetVariable",
            "inputs": {
              "name": "ArrayOfObjects",
              "value": "@outputs('RemoveFoundElement')"
            },
            "runAfter": {
              "RemoveFoundElement": [
                "Succeeded"
              ]
            },
            "metadata": {
              "operationMetadataId": "e50136bb-7b12-42db-acc4-70338d823317"
            }
          },
          "ArrayOfValues": {
            "type": "Select",
            "inputs": {
              "from": "@variables('ArrayOfObjects')",
              "select": "@item()[outputs('Settings')['Attribute']]"
            },
            "runAfter": {},
            "description": "item()[outputs('Settings')['Attribute']]",
            "metadata": {
              "operationMetadataId": "ad3f6b94-8dc5-489b-9210-38e3671c8255"
            }
          },
          "Prepare": {
            "type": "Compose",
            "inputs": {
              "Root": {
                "Values": "@variables('ArrayOfObjects')"
              }
            },
            "runAfter": {
              "ArrayOfValues": [
                "Succeeded"
              ]
            },
            "metadata": {
              "operationMetadataId": "ebf6f3bb-6669-4792-be20-de49ce49f573"
            }
          },
          "XML": {
            "type": "Compose",
            "inputs": "@xml(outputs('Prepare'))",
            "runAfter": {
              "Prepare": [
                "Succeeded"
              ]
            },
            "description": "xml(outputs('Prepare'))",
            "metadata": {
              "operationMetadataId": "c8af3e8c-24b8-4b8e-b76c-893389262fcc"
            }
          },
          "Append_to_Sorted_Array": {
            "type": "AppendToArrayVariable",
            "inputs": {
              "name": "Sorted",
              "value": "@variables('ArrayOfObjects')[outputs('ElementPosition')]"
            },
            "runAfter": {
              "ElementPosition": [
                "Succeeded"
              ]
            },
            "metadata": {
              "operationMetadataId": "48c26b4c-094e-4928-9fde-4c6c6b700cb3"
            }
          },
          "ElementPosition": {
            "type": "Compose",
            "inputs": "@if\r\n(\r\n  equals(outputs('Settings')['SortOrder'], 'Asc'),\r\n  int(xpath(outputs('XML'), concat('count(//Root/Values[', outputs('Settings')['Attribute'] ,'= ', min(body('ArrayOfValues')),']/preceding-sibling::*)'))),  \r\n  int(xpath(outputs('XML'), concat('count(//Root/Values[', outputs('Settings')['Attribute'] ,'= ', max(body('ArrayOfValues')),']/preceding-sibling::*)')))  \r\n)",
            "runAfter": {
              "xpath_expression": [
                "Succeeded"
              ]
            },
            "metadata": {
              "operationMetadataId": "4c6aa711-ae8f-44f5-b460-1baa6921dae1"
            }
          },
          "xpath_expression": {
            "type": "Compose",
            "inputs": "@if(\r\n  equals(outputs('Settings')['SortOrder'], 'Asc'),\r\n  concat('count(//Root/Values[', outputs('Settings')['Attribute'] ,'= ', min(body('ArrayOfValues')),']/preceding-sibling::*)'),  \r\n  concat('count(//Root/Values[', outputs('Settings')['Attribute'] ,'= ', max(body('ArrayOfValues')),']/preceding-sibling::*)')  \r\n)",
            "runAfter": {
              "XML": [
                "Succeeded"
              ]
            }
          }
        },
        "runAfter": {
          "Settings": [
            "Succeeded"
          ]
        },
        "description": "range(0,length(variables('ArrayOfObjects')))",
        "metadata": {
          "operationMetadataId": "f5e1ebe2-13e2-4c60-a619-7e74808a028c"
        }
      }
    },
    "runAfter": {
      "Initialize_Sorted": [
        "Succeeded"
      ]
    }
  }
}

Once you have copied the code above you can simply paste it into your own flow by adding a new action, going to My clipboard and then press CTRL-V and you will see the scope appear:

Detail of how the flow works

For each iteration of the loop, the variable ArrayOfObjects is converted to XML. The example data produces this XML on the first iteration of the loop:

<Root>
  <Values>
	<label>Car</label>
	<score>0.26</score>
  </Values>
  <Values>
	<label>Bus</label>
	<score>0.26</score>
  </Values>
  <Values>
	<label>Train</label>
	<score>0.34</score>
  </Values>
  <Values>
	<label>Boat</label>
	<score>0.45</score>
  </Values>
  <Values>
	<label>Truck</label>
	<score>0.62</score>
  </Values>
  <Values>
	<label>Bike</label>
	<score>0.11</score>
	</Values>
</Root>

During this first run it finds the highest value in the score attribute of the array. It does this using a dynamic xpath expression. Inside the scope there is compose action called xpath expression, the value of which is never used again, but I left it in place so that you can see what the flow is doing. In the first iteration the xpath produced is:

count(//Root/Values[score= 0.62]/preceding-sibling::*)

If you copy the XML above into xpather.com and then copy the expression to the expression box you will see that it returns a value of 4. The number 4 in this case represents the count of array elements before the target value was found.

The array position is then used for two purposes:

  • To reconstruct the original array so that contains all of the elements except the one which was found.
  • To add the found element to a new array.

In the next iteration of the loop, the found value is no longer present in the original array. This process keeps going until there is only one element left in the array.

Finding the top three values

Although I have explained how to sort an array. The original posters question was just to find the top three values. There are two ways this flow can be modified to achieve this:

Modify the apply to each loop

The source for the apply to each loop uses the following expression:

range(0,length(variables('ArrayOfObjects')))

With our sample data, this will produce the following JSON array:

[ 0, 1, 2, 3, 4, 5 ]

To get only the top 3 from the original array, the expression for the apply to each loop could be changed to:

range(1,3)

This will produce the fastest result as the apply to each loop will run only as many times as required to produce the Top 3 entries.

Using the take expression

The other alternative is to leave the scope to sort the entire array and when it is finished use the take expression within a compose action to extract the first 3 entries:

take(variables('Sorted'), 3)

This is the best method to use if you want an entire sorted array as opposed to a Top X.

Conclusion

This is a fairly advanced flow but it is easy to implement by copying the scope code. It would nice if there was a native sort action, if you feel the same, please add your vote to the idea on the forum.

Filed Under: Power Platform Tagged With: Power Automate

Add working days to a date in a Power Automate Flow

April 5, 2022 by Paulie 6 Comments

In this post I will show you a Power Automate Flow you can use to add days to a given date and exclude weekends, or specific dates from the result. I was inspired to do my own solution by some recent posts by others:

Tom Riha – How to add only working days to a date using Power Automate
Matthew Devaney – Power Apps Calculate Business Days Excluding Weekends & Holidays

Both of these guys have brilliant blogs and I highly recommend subscribing to both of them. It is really interesting to see how we all approached the problem slightly differently.

Power Automate includes the useful expression addDays which will add a specified number of days to a date that you provide. While this function is useful, it does not allow you to exclude certain days. Such as weekends or public holidays.

Basic Flow – Add days excluding weekends

First, add two compose actions, name the first one Date and the second one DaysToAdd.

Power Automate Compose Actions to setup the date additions.

Next we will generate a range of dates for the next 365 days and also what day of the week they represent. Add a Select action and name it Dates:

The expressions used in the select:

From: range(0,365)
Date: formatDateTime(addDays(outputs('Date'),item()), 'yyyy-MM-dd')
Day: dayOfWeek(addDays(outputs('Date'),item()))

A sample of the output of the select action will be a range of 365 dates, something like this:

[
  {
    "Date": "2022-04-04",
    "Day": 1
  },
  {
    "Date": "2022-04-05",
    "Day": 2
  },
  {
    "Date": "2022-04-06",
    "Day": 3
  },
  {
    "Date": "2022-04-07",
    "Day": 4
  },
  {
    "Date": "2022-04-08",
    "Day": 5
  },
  {
    "Date": "2022-04-09",
    "Day": 6
  }
]

As you can see, we are generating an array of dates, and the day of the week that date represents (0 being Sunday and 6 being Saturday).

Next, add a Filter Array step, to reduce the dates array so that it only includes week days:

The expression used in the Filter is:

@and(not(equals(item()['day'], 0)), not(equals(item()['day'], 6)))

Now we have a date array that contains only weekdays, we just need to pick out our target date. Add a compose action called TargetDate:

The expression used here is:

body('Filter_array')[outputs('DaysToAdd')]['date']

This expression will return just the date that we are looking to find, here is the output from my example:

The TargetDate compose action is optional, you can simply use the expression shown in any of your actions.

Complete Code for Basic Example

If you prefer, you can simply copy the code below into your clipboard and paste it into your own flow:

{
	"id": "df633c17-f74d-48fa-bd16-e67fbcc8eeef",
	"brandColor": "#8C3900",
	"connectionReferences": {},
	"connectorDisplayName": "Control",
	"icon": "data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=",
	"isTrigger": false,
	"operationName": "Add_Days",
	"operationDefinition": {
		"type": "Scope",
		"actions": {
			"Date": {
				"type": "Compose",
				"inputs": "2022-04-04",
				"runAfter": {},
				"metadata": {
					"operationMetadataId": "f03622ae-0e01-4a52-957b-476f810aea4d"
				}
			},
			"DaysToAdd": {
				"type": "Compose",
				"inputs": 10,
				"runAfter": {
					"Date": ["Succeeded"]
				},
				"metadata": {
					"operationMetadataId": "22b2d012-3447-4ac0-80a4-24251d06ff99"
				}
			},
			"Dates": {
				"type": "Select",
				"inputs": {
					"from": "@range(0,365)",
					"select": {
						"Date": "@formatDateTime(addDays(outputs('Date'),item()), 'yyyy-MM-dd')",
						"Day": "@dayOfWeek(addDays(outputs('Date'),item()))"
					}
				},
				"runAfter": {
					"DaysToAdd": ["Succeeded"]
				},
				"description": "range(0,365)",
				"metadata": {
					"operationMetadataId": "362d7f26-03cb-4a8c-a70e-8e75019a1061"
				}
			},
			"Filter_array": {
				"type": "Query",
				"inputs": {
					"from": "@body('Dates')",
					"where": "@and(not(equals(item()['day'], 0)), not(equals(item()['day'], 6)))"
				},
				"runAfter": {
					"Dates": ["Succeeded"]
				},
				"metadata": {
					"operationMetadataId": "bc17d8a4-b5b8-4a5b-bd3a-34fcd2d0f2ae"
				}
			},
			"TargetDate": {
				"type": "Compose",
				"inputs": "@body('Filter_array')[outputs('DaysToAdd')]['date']",
				"runAfter": {
					"Filter_array": ["Succeeded"]
				},
				"description": "body('Filter_array')[outputs('DaysToAdd')]['date']",
				"metadata": {
					"operationMetadataId": "19be198e-647c-40bf-9109-86261450a9ef"
				}
			}
		},
		"runAfter": {},
		"metadata": {
			"operationMetadataId": "ff58b7e7-a16e-4a1c-9ffe-b377e6b228ae"
		}
	}
}

Add Filtering of Public Holidays

It is possible to enhance the flow above so that it will also exclude public holidays in addition to the weekends.

First, setup a SharePoint list that contains all of the dates you want to skip. I setup my SharePoint list with two columns. Title and HolidayDay. This is how my list looks:

SharePoint list showing public holidays which will be excluded from the result.

Next we can modify our flow to also exclude these dates. We need to add an additional three actions:

First, add a Get Items (SharePoint) action right below the existing Filter Array action and point it at your site and your Holiday Calendar list.

Then add a Select action, rename it to HolidayDays. Modify the select action so that it is in text mode by clicking the small grid icon on the right side of the Map field. Populate the From field with the value from the Get Items action. In the Map field choose the HolidayDay.

Next add a filter action and rename it to ExcludeHolidays. The from will be the body output of our original filter array. On the left side of the filter we choose the output from the HolidayDays action and choose the option does not contain on the filter. On the right hand side of the filter use the expression item()[‘Date’] – It is much easier to comprehend in a image:

Finally, modify the expression in the TargetDate compose action to:

body('ExcludeHolidays')[outputs('DaysToAdd')]['date']

Now your TargetDate output will exclude both weekends and any public holidays that you have specified.

Filed Under: Power Platform Tagged With: Power Automate

How to Bulk Delete Records from Dataverse with Power Automate

November 16, 2021 by Paulie 6 Comments

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
  • Azure Application Registration
  • Collect required information
    • Full Dataverse Environment
    • Dataverse for Teams Environment
    • Office 365 Tennant and Application ID
  • Implement the Power Automate Flow
    • Batch Delete Scope Code
    • Configure the Settings Action
    • Configure the List Rows action.
  • Conclusion

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:

Image of an example Power Automate Flow to delete records from a Dataverse table.

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.
Image of Azure App Registration for Web API Access to Dataverse

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:

Image of API Permissions being set on an Azure App Registration

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

Image of Admin Consent being granted on an Azure App Registration.

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

Image of configuration of an Azure App Registration

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:

Image of Power Platform Admin center being used to find the environment URL .

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:

Image of Microsoft Teams Power Apps session details, for retrieving the Tennant ID and Environment ID

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:

Image of Azure App Registration. To retrieve the Application ID and Tenant ID.

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:

Image of a Power Automate Flow that uses the batch API to bulk delete records from a Dataverse table.

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": "[email protected]{outputs('settings')['applicationID']}&scope=https://@{outputs('settings')['org']}//user_impersonation &[email protected]{outputs('settings')['userName']}&[email protected]{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": "[email protected]{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;[email protected]{actions('settings')?['trackedProperties']['batchGUID']}",
								"authorization": "Bearer @{body('HTTPAuth')['access_token']}",
								"OData-Version": "4.0",
								"OData-MaxVersion": "4.0"
							},
							"body": "[email protected]{actions('settings')?['trackedProperties']['batchGUID']}\nContent-Type: multipart/mixed; [email protected]{actions('settings')?['trackedProperties']['changeSetGUID']}\n\[email protected]{join(body('Select'), decodeUriComponent('%0A'))}\[email protected]{actions('settings')?['trackedProperties']['changeSetGUID']}--\[email protected]{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.

Image of a Power Automate scope being pasted into a 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.

Filed Under: Power Platform Tagged With: Power Automate

How to access PowerApps Studio when developing a Teams App

August 28, 2021 by Paulie Leave a Comment

When developing an app in PowerApps for Teams, you are restricted to the editing the app within Teams. This is tolerable for a basic app, but quickly becomes frustrating if you are spending a lot of time developing an app.

I have learned to put up with this, but really wanted access to the normal interface. A recent update to PowerApps studio meant I had to change the Authoring version of a teams based app but I was unable to do that via the Teams interface. So this made me even more determined to find a way to access the normal Power Apps Studio.

It is actually quite simple to do and you just need to form the correct URL to access Power Apps Studio for a Teams based App. You need a couple of bits of information:

  • The Environment ID the Team containing the App.
  • The App ID.

Start by going into the Power Apps Tab within Teams, then click on build, and select the Team containing the app that you want to work on.

Image of Power Apps Build Tab in Microsoft Teams

Get the Environment ID

There are many ways to get the environment ID, but from here, simply click on the ellipses on the right hand pane and choose “Open in Power Apps”. This will launch a browser window with a URL such as:

https://make.powerapps.com/environments/7eb61244-9d3f-455f-8b52-112c17c3dac2/solutions

Take the environment ID from the URL and close the browser to go back to Teams.

Collect the App ID

To collect the ID of the Power App do the following:

  • In the default “Built by this team” tab, click on the See all link:
  • This will show you all of the objects associated with the team. Click on the ellipses next to the app that you want to edit and click Details:
  • Copy the App ID from the Details page:

Now that you have both the Environment ID and the App ID you can formulate the correct URL to edit the app directly in your browser:

https://make.powerapps.com/e/[Environment]/canvas/?action=edit&app-id=%2Fproviders%2FMicrosoft.PowerApps%2Fapps%2F[App ID]

Simply replace [Environment] and [App ID] with the appropriate values. You can also specify what version of the studio you would like to use by using a URL like this:

https://make.powerapps.com/e/[Environment]/canvas/?action=edit&app-id=%2Fproviders%2FMicrosoft.PowerApps%2Fapps%2F[App ID]&studio-version=v3.21082.34.197169591

To make things a bit easier for you, you can just put the Environment and the App ID into the boxes below and the URL will be generated for you.

Power App Studio URL:

In my opinion, this makes editing Teams based apps so much easier. I wonder if it was obvious to everyone else how to do this already and I just missed it! Hope this is helpful to you!

Filed Under: Power Platform Tagged With: PowerApps

  • Go to page 1
  • Go to page 2
  • Go to page 3
  • Interim pages omitted …
  • Go to page 10
  • Go to Next Page »

Primary Sidebar

Link to my LinkedIn Profile
Buy me a coffee

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 270 other subscribers.

Go to mobile version