• Skip to primary navigation
  • Skip to main content

Tachytelic.net

  • Get in Touch
  • About Me

Microsoft Power Automate (Microsoft Flow)

All the content that I have written related to Microsoft Power Automate (aka Microsoft Flow). Please click here to learn more about what Power Automate is, and how it can help your business.

I hope you find something that is useful, if you need further assistance please don't hesitate to get in touch!

Add working days to a date in a Power Automate Flow

April 5, 2022 by Paulie 5 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, Uncategorized 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 export data in Power Automate to an Excel File

August 25, 2021 by Paulie 8 Comments

In this post I will show you how to export data from Power Automate, to an Excel file. This would seem to be a straightforward requirement, but until recently has required the use of apply to each loops which are slow and can potentially use many API actions.

Table of contents

  • Create an Excel Export Template
  • Convert the Template to Base64
  • Flow Overview
  • Use the Select Action to Reshape your export Data
  • Export to Excel Done!

Video Demonstration

In this demonstration I am exporting 2,000 records from a SharePoint list to an Excel document and the flow takes only 5 seconds to execute:

Create an Excel Export Template

The first step in this process is to create an Excel that contains a table for your exported data to be inserted into. Format the cells and add formulas as per your requirements and then save the file. In my example, the file looks like this:

Image of an Excel table which is going to be populated with data exported from Power Automate

As you can see from the screenshot, the table is called InvoiceExport and it has four fields. The fields containing values have had number formatting applied and there are some formulas to sum the Invoice Amount and count the number of records.

Make a note of what you have called the table, you will need it for your flow.

Convert the Template to Base64

Once you are happy with your Excel template. Head to Base 64 Guru and upload your template file. Base64 Guru will create a base64 representation of your file, like this:

Image of base64.guru website, converting an Excel file to Base64

Click on the copy button above the Base64 representation of your file to copy it to your clipboard.

Flow Overview

To give you an idea of how simple this flow is to build, here is a screenshot of the entire thing, it consists of only 5 actions:

Image of a Power Automate Flow that will export data to an Excel file.

Use the Select Action to Reshape your export Data

In my example, the data source is SharePoint, but any data source accessible to Power Automate will work. It could be SQL Server, Dataverse, SharePoint or even another Excel file. The purpose of the select action is to reshape the data down to just the columns that you want to export.

Within the select action create a raw array of values that match the order of the columns of your Excel template. We do not want to name the columns.

The code for my select action looks like this:

[
  @{item()?['Title']},
  @{item()?['Customer']},
  @{item()?['Invoice_x0020_Amount']},
  @{item()?['Date']}
]

Select Action Text Mode

Your Select action may look a different to mine, because the default mode is Key/Value mode.

You can switch to Text mode by clicking on this:

Create the Excel File and Populate with your Data

Next step is to create your Excel file and populate it with the data from your Select action:

First, create a Compose action called ExcelDoc and paste in the Base64 representation of your template file.

Next, use a Create File action, name the file as per your requirement and for the file content use the expression:

base64ToBinary(outputs('ExcelDoc'))

Finally, create add a new action by searching for HTTP and you should see this action:

Image of the "Send a HTTP Request" action in Power Automate

At the time of writing this post, this is a preview action and it is not available in all Office 365 Tenants. If you do not see it in your list of actions, simply copy the code below:

{
  "id": "c78ee521-4c62-4f34-84b1-8ef7-98516f0f",
  "brandColor": "#EB3C00",
  "connectionReferences": {},
  "connectorDisplayName": "Office 365 Groups",
  "icon": "https://connectoricons-prod.azureedge.net/releases/v1.0.1504/1.0.1504.2515/office365groups/icon.png",
  "isTrigger": false,
  "operationName": "Send_a_HTTP_request",
  "operationDefinition": {
    "type": "OpenApiConnection",
    "inputs": {
      "host": {
        "connectionName": "shared_office365groups",
        "operationId": "HttpRequest",
        "apiId": "/providers/Microsoft.PowerApps/apis/shared_office365groups"
      },
      "parameters": {
        "Uri": "",
        "Method": "GET",
        "ContentType": "application/json"
      },
      "authentication": {
        "type": "Raw",
        "value": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"
      }
    },
    "runAfter": {
      "Create_file": [
        "Succeeded"
      ]
    }
  }
}

And then add a new action to your flow, go to My Clipboard and press CTRL-V. The new action will then appear in your clipboard and you can add it to your flow:

Once the new action is in place, configure it as follows:

URI:

https://graph.microsoft.com/v1.0/me/drive/items/@{substring(outputs('Create_file')?['body/Id'], add(indexOf(outputs('Create_file')?['body/Id'], '.'),1))}/workbook/tables/InvoiceExport/rows/add

Notice that I have put the name of the table into the URL:

/workbook/tables/InvoiceExport/rows/add

You will need to update this to reflect the name of your table.

Method: POST

Body:

{
	"values": @{outputs('select')['body']}
}

Export to Excel Done!

With this new action it is possible to add many rows to an Excel file very quickly and use only a single API action to do so. I tested this with 2,000 rows and it took 5 seconds to complete.

Once the export is complete you can take whatever action you want to with the file. For example to email it you could do a Get File Content and an email action:

Image of Power Automate emailing data exported to an Excel file.

This is a really fast, and easy way to export data from Power Automate to Excel. Please let me know if in the comments if you get stuck or found this useful.

Filed Under: Power Platform Tagged With: Power Automate

How to create a Zip file in Power Automate for free

July 26, 2021 by Paulie 31 Comments

In this post, I will explain how to create a Zip file in Power Automate without the use of any third party connectors or premium actions.

Table of contents

  • Introduction
  • Flow Detail
  • Flow Code and Implementation
  • Additional Information
  • Conclusion

Introduction

Power Automate includes functionality to extract data from Zip files. The Zip file contents can be extracted to a OneDrive or SharePoint folder:

Image showing actions in Power Automate that extract the contents of Zip files.

It’s strange that there is not a matching create archive action. An idea was posted on the community forum in 2017, but it has not been added, nor received many votes. Encodian provide this function, but many people prefer not to use external connectors.

SharePoint provides this functionality through the user interface, and I wondered if this API endpoint could be accessed from directly from Power Automate.

Image of SharePoint Online interface that allows you to create a Zip file by selecting multiple files.
By selecting multiple files in SharePoint and pressing download, a Zip file will be generated and downloaded to you.

I quickly found that it is possible to create Zip files directly from Power Automate!

The key to unlocking this functionality is in the SharePoint API action RenderListDataAsStream. I used this API action in a previous post, easy way of getting Totals from SharePoint lists. This API action can return some useful information, such as:

  • The Access Token required to access the items.
  • The size of each file.
  • The full item URL.
  • The media URL – which is used for converting and manipulating files.

By collecting this information it is possible to formulate a new request to the media conversion enpoint which can generate a Zip File.

Flow Detail

Before we get into the details, here is a screenshot of a flow which zips an entire folder:

Image of a Power Automate Flow which creates a Zip File.

Although not as convenient as a built-in action, this flow isn’t very complicated. At the end of the flow, the zip file is written to a SharePoint Document Library and a OneDrive folder.

Flow Code and Implementation

To implement this flow for yourself, you need to do the following:

  • Copy and paste the Scope Code below into one of your own flows.
  • Update the references to SharePoint and OneDrive to match your own environment.
  • Change the values in the compose action called settings
    • libraryPath is the path to the root of the SharePoint site.
    • zipFolderPath is the relative path of the folder that you want to Zip.
  • Update or delete the final storage actions appropriately for your implementation.

Scope Code:

{
  "id": "b7af8f36-c669-4d9f-bb7d-bf14-63edb83a",
  "brandColor": "#8C3900",
  "connectionReferences": {
    "shared_onedriveforbusiness": {
      "connection": {
        "id": "/providers/Microsoft.PowerApps/apis/shared_onedriveforbusiness/connections/shared-onedriveforbu-05f1da5e-297d-4a80-8df7-cb78-b654b8a3"
      }
    },
    "shared_sharepointonline": {
      "connection": {
        "id": "/providers/Microsoft.PowerApps/apis/shared_sharepointonline/connections/de645f5680b74c47bbce762c8e2d06ac"
      }
    }
  },
  "connectorDisplayName": "Control",
  "icon": "data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=",
  "isTrigger": false,
  "operationName": "CreateZip",
  "operationDefinition": {
    "type": "Scope",
    "actions": {
      "accessToken": {
        "type": "Compose",
        "inputs": "@outputs('SharePointHTTP')?['body']['ListSchema']['.driveAccessToken']",
        "runAfter": {
          "SharePointHTTP": [
            "Succeeded"
          ]
        },
        "description": "Collects the Access Token required to access the files"
      },
      "Select": {
        "type": "Select",
        "inputs": {
          "from": "@body('SharePointHTTP')['ListData']['Row']",
          "select": {
            "name": "@item()['FileLeafRef']",
            "size": "@item()['SMTotalSize']",
            "docId": "@{item()['.spItemUrl']}&@{outputs('accessToken')}",
            "isFolder": "@if(equals(item()['FSObjType'],'1'), true, false)"
          }
        },
        "runAfter": {
          "accessToken": [
            "Succeeded"
          ]
        },
        "description": "Reformats the output of SharePoint HTTP action ready for submission to the Zip endpoint"
      },
      "Attachment_Items": {
        "type": "Compose",
        "inputs": {
          "items": "@body('Select')"
        },
        "runAfter": {
          "Select": [
            "Succeeded"
          ]
        },
        "description": "Additional formatting of the Select array"
      },
      "downloadZip": {
        "type": "OpenApiConnection",
        "inputs": {
          "host": {
            "connectionName": "shared_sharepointonline",
            "operationId": "HttpRequest",
            "apiId": "/providers/Microsoft.PowerApps/apis/shared_sharepointonline"
          },
          "parameters": {
            "dataset": "@body('SharePointHTTP')['ListSchema']['.mediaBaseUrl']",
            "parameters/method": "POST",
            "parameters/uri": "/transform/[email protected]{body('SharePointHTTP')['ListSchema']['.callerStack']}",
            "parameters/headers": {
              "Content-Type": "application/x-www-form-urlencoded"
            },
            "parameters/body": "zipFileName=test.zip&[email protected]{guid()}&provider=spo&[email protected]{encodeUriComponent(outputs('Attachment_Items'))}&oAuthToken="
          },
          "authentication": {
            "type": "Raw",
            "value": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"
          }
        },
        "runAfter": {
          "Attachment_Items": [
            "Succeeded"
          ]
        },
        "description": "Submits the request to the media server and downloads the Zip file"
      },
      "StoreZip": {
        "type": "OpenApiConnection",
        "inputs": {
          "host": {
            "connectionName": "shared_onedriveforbusiness",
            "operationId": "CreateFile",
            "apiId": "/providers/Microsoft.PowerApps/apis/shared_onedriveforbusiness"
          },
          "parameters": {
            "folderPath": "/__PA_Test/Filled Word Docs",
            "name": "testFile.zip",
            "body": "@base64ToBinary(body('downloadZip')['$content'])"
          },
          "authentication": {
            "type": "Raw",
            "value": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"
          }
        },
        "runAfter": {
          "downloadZip": [
            "Succeeded"
          ]
        },
        "description": "Stores the received Zip file in OneDrive",
        "runtimeConfiguration": {
          "contentTransfer": {
            "transferMode": "Chunked"
          }
        }
      },
      "settings": {
        "type": "Compose",
        "inputs": {
          "libraryPath": "/sites/PowerAutomateText/Shared Documents",
          "zipFolderPath": "/DeclarationTemplateFilled"
        },
        "runAfter": {},
        "description": "Set library path to the location of the SharePoint document library that contains the documents for compression. Set zipFolderPath to the relative path of the Folder that you want to archive."
      },
      "SharePointHTTP": {
        "type": "OpenApiConnection",
        "inputs": {
          "host": {
            "connectionName": "shared_sharepointonline",
            "operationId": "HttpRequest",
            "apiId": "/providers/Microsoft.PowerApps/apis/shared_sharepointonline"
          },
          "parameters": {
            "dataset": "https://accendo1.sharepoint.com/sites/PowerAutomateText",
            "parameters/method": "POST",
            "parameters/uri": "_api/web/GetListUsingPath([email protected])/[email protected]=%[email protected]{encodeUriComponent(outputs('settings')['libraryPath'])}%27&[email protected]{encodeUriComponent(concat(outputs('settings')['libraryPath'], outputs('settings')['zipFolderPath']))}",
            "parameters/body": "{\"parameters\": {\"RenderOptions\": 4103}}"
          },
          "authentication": {
            "type": "Raw",
            "value": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"
          }
        },
        "runAfter": {
          "settings": [
            "Succeeded"
          ]
        },
        "description": "Retrieves the file and folder information required to create a Zip file"
      },
      "Create_file": {
        "type": "OpenApiConnection",
        "inputs": {
          "host": {
            "connectionName": "shared_sharepointonline",
            "operationId": "CreateFile",
            "apiId": "/providers/Microsoft.PowerApps/apis/shared_sharepointonline"
          },
          "parameters": {
            "dataset": "https://accendo1.sharepoint.com/sites/PowerAutomateText",
            "folderPath": "/Shared Documents",
            "name": "testWordFile.docx",
            "body": "@base64ToBinary(body('downloadZip')['$content'])"
          },
          "authentication": {
            "type": "Raw",
            "value": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"
          }
        },
        "runAfter": {
          "StoreZip": [
            "Succeeded"
          ]
        },
        "description": "Stores the received Zip file in a SharePoint document library",
        "runtimeConfiguration": {
          "contentTransfer": {
            "transferMode": "Chunked"
          }
        }
      }
    },
    "runAfter": {},
    "description": "A scope to create a Zip file in Power Automate without the use of 3rd Party Connectors or Premium Actions"
  }
}

Additional Information

This flow works by sending an array of files or folder names to the media conversion endpoint as form data. A sample array looks like this:

{
  "items": [
    {
      "name": "_rels",
      "size": "1168",
      "docId": "https://accendo1.sharepoint.com:443/_api/v2.0/drives/b!wGO-FC/items/01KW24YP...?version=Published&access_token=eyJ0eXAi...",
      "isFolder": true
    },
    {
      "name": "docProps",
      "size": "2861",
      "docId": "https://accendo1.sharepoint.com:443/_api/v2.0/drives/b!wGO-FC/items/01KW24YP...?version=Published&access_token=eyJ0eXAi...",
      "isFolder": true
    },
    {
      "name": "word",
      "size": "477746",
      "docId": "https://accendo1.sharepoint.com:443/_api/v2.0/drives/b!wGO-FC/items/01KW24YP...?version=Published&access_token=eyJ0eXAi...",
      "isFolder": true
    },
    {
      "name": "[Content_Types].xml",
      "size": "2703",
      "docId": "https://accendo1.sharepoint.com:443/_api/v2.0/drives/b!wGO-FC.../items/01KW24YP...?version=Published&access_token=eyJ0eXAi...",
      "isFolder": false
    }
  ]
}

This array is created in the Select action based on the output of the SharePointHTTP action. This example flow is designed to zip an entire folder but you might have more specific requirements.

If you want to filter this array you have a choice of two places in which to do it:

  • By modifying the URI in the SharepointHTTP action to filter what it returns in the request (Examples here).
  • By adding a filter array action after the Select action and then using that filter as the input for the Compose action called Attachment Items.

Filtering the Select action is the easiest option, but less efficient.

Conclusion

I’ve been looking for a way to a create Zip file in Power Automate for a while (in order to be able to modify Word documents) so it is great to get this done.

Although this solution works well, It’s clear that a native function to do this wouldn’t be much work, so please vote the idea up if you would like to see this.

Filed Under: Power Platform Tagged With: Power Automate

Calculate the Sum for a SharePoint column in Power Automate

July 13, 2021 by Paulie 8 Comments

I’ve already written a couple of posts about how to sum arrays in Power Automate, but this post is specifically about how to sum a column from a SharePoint list. If you want to sum an array that has not originated from a SharePoint list, check out my other posts:

  • Instantly sum an array with Power Automate
  • How to Sum an Array of Numbers in Power Automate

It has always surprised me that there isn’t an easy way to do this built into Power Automate, but I stumbled across a simple solution. I was working with a list that had a view with a Total applied to it and it occurred to me that if the total is available in a view, then it must also be available via an API.

This same method can be used to perform many different types of aggregations on SharePoint lists.

So the first step in this process is to modify your SharePoint List view so that it has a total applied to it.

Get the Colum Total from the SharePoint List View

Once you have modified your view to have a total you need to collect three bits of information:

  • The Site Path.
  • The List Name.
  • The View ID.

Once you have that information create a compose action called Settings and populate it with the information you collected above. Here is the code from my example:

{
  "sitePath": "/sites/PowerAutomateText/",
  "listName": "Invoices",
  "viewID": "576393ba-5827-4100-a157-7d901b344ca8"
}

and a screenshot of the action:

Image of Power Automate Compose action which holds the settings for the flow.

Next add a Send an HTTP request to SharePoint action, select the site address and set the method to POST, in the Uri field paste the following code:

_api/web/lists/GetByTitle('@{outputs('Settings')['listName']}')/[email protected]{outputs('Settings')['viewID']}

As you can see, I am using the RenderListDataAsStream API action, which you can do a lot with and it is worth reading the documentation.

In the headers section add a header named accept with the value of application/json;odata=nometa

The action should look like this:

Image of SharePoint HTTP Request that is used to calculate a column total.

This action will bring you back everything that you would see in the browser when looking at the same view. It is important to understand that this will bring view results, not the standard list of items that you would receive with the Get Items action.

The output of this action produces an array called Row which in addition to the contents of the view shows the aggregated total on each row. Here is a sample of the output produced by my action:

{
	"Row": [{
		"ID": "27694",
		"PermMask": "0x7ffffffffffbffff",
		"FSObjType": "0",
		"UniqueId": "{7CF06512-7444-441F-9D6B-08610D1BB415}",
		"ContentTypeId": "0x0100EA3A251D88BC5941A590C41BA4B80EE700F04B73CD6F513D44943FDF3FE121DF77",
		"FileRef": "/sites/PowerAutomateText/Lists/Invoices/27694_.000",
		"FileRef.urlencode": "%2Fsites%2FPowerAutomateText%2FLists%2FInvoices%2F27694%5F%2E000",
		"FileRef.urlencodeasurl": "/sites/PowerAutomateText/Lists/Invoices/27694_.000",
		"FileRef.urlencoding": "/sites/PowerAutomateText/Lists/Invoices/27694_.000",
		"Attachments": "0",
		"SMTotalSize": "218",
		"_CommentFlags": "",
		"_CommentCount": "",
		"InvNum": "1",
		"InvNum.": "1.00000000000000",
		"Customer": "Ledner Inc",
		"Invoice_x0020_Amount": "804.39",
		"Invoice_x0020_Amount.": "804.390000000000",
		"Date": "6/25/2015",
		"Date.": "2015-06-25T07:00:00Z",
		"ItemChildCount": "0",
		"FolderChildCount": "0",
		"owshiddenversion": "1",
		"Restricted": "",
		"Invoice_x0020_Amount.SUM": "5,027,707.52"
	}]
}

As you can see, on line 27 of the output there is a an object element named Invoice_x0020_Amount.SUM which we can get with a simple compose action:

Image of a Power Automate compose action which is extracting the sum of a SharePoint Column

Because the result from SharePoint is an array, the compose action just takes the first result and retrieves the Total from there, here is the code I used as reference. Obviously you will need to update it to reflect whatever your field name is:

float(outputs('Send_an_HTTP_request_to_SharePoint')?['body']['Row'][0]['Invoice_x0020_Amount.SUM'])

Other Possible Uses

This technique can be used in a number of other ways to get results that would otherwise require much more effort. The standard aggregations in a SharePoint view are:

  • Count
  • Average
  • Maximum
  • Minimum
  • Sum
  • Std Deviation
  • Variance

But in addition to those aggregations you can also perform group by operations. So if I was to create a view on the same list, but group it by customer name, I could get the Total Invoice value on a customer by customer basis.

Conclusion

I think this is the easiest way to calculate the sum of a SharePoint column. My other post on using the xpath method is also good, but not specific to SharePoint columns

Filed Under: Power Platform Tagged With: Power Automate

  • Go to page 1
  • Go to page 2
  • Go to page 3
  • Interim pages omitted …
  • Go to page 9
  • Go to Next Page »
Go to mobile version