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

Tachytelic.net

  • Get in Touch
  • About Me

Power Platform

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 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

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

  • 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 245 other subscribers.

Go to mobile version