Table of contents

• Collection expressions
• Union
• Chunk
• Conversion expression
• uriComponent
• Format numbers
• DateTime
• ConvertFromUtc / ConvertToUtc
• FormatDateTime
• Manipulation expressions
• Coalesce
• xpath
• xpath loop in an arrays
• Creating a csv with special or language specific characters
• Miscellaneous
• Dynamically insert expressions into other expressions
• Dynamic Table Range for Excel
• Style tables created in “Create HTML Table” action
• Get the first day of a week
• Injecting a index into an array
• Using item() in a for each loop
• URL to access an app
• Extracting the site url from manual SharePoint trigger
• Workflow()

Collection expressions

Union

If you collect users emails or objects into an array variable, you might end up with duplicates in your array.

Solution: Union will remove all duplicates from your array.

How to use: Take your array and add it twice to your union.

union(variables('arrUsers'),variables('arrUsers'))

Chunk

You want to split your array of users into even groups of 5 users. I had that use case building a Power App that groups a list of people into even groups.

Solution: Chunk will divide your array into groups of any length or size. If the size of your chunks doesn’t perfectly add up, the last array will contain at the left overs.

How to use: Take your array, then add the number of objects per chunk. It will return an array with arrays, containing the users, in the specified size. You can access the chunks using the index numbers of the arrays.

chunk(variables('arrUsers'),5)

Conversion expression

uriComponent

Working with the Convert HTML to text in Power Automate might bring you a lot of special characters that are not always visible. This can be very frustrating using that text in following actions in your flow.

Solution: uriComponent will make all special characters visible. For example: a “ “ (space) will turn into %20 and a “&” into %26. More here

How to use: Add your text into the expression using a compose action. Use the output to understand your text value. Then use the second expression to return your string back to normal.

uriComponent(variables('strHtmlText'))
uriComponentToString(outputs('Compose - HTML URI'))

Format numbers

Link to documentation: Reference guide for expression functions - Azure Logic Apps | Microsoft Learn

formatNumber(3,'D2')
-> 03
formatNumber(3,'D4')
-> 0003
formatNumber(3.141592653589793238,'F2')
-> 3.14
formatNumber(3,'F2')
-> 3.00
formatNumber(3.141592653589793238,'F4')
-> 3.1416 //with auto round up
formatNumber(1800,'C2','de-DE')
-> 1.800,00€

DateTime

ConvertFromUtc / ConvertToUtc

SharePoint Online always stores dates in times in UTC timezone format. To convert from that the any specific timezone you can use actions or this expression. For the syntax you will need the correct timezone names. Optionally you can output in a specific format. The same goes for converting time to utc, you will have to specify a time zone using the correct name.

//format into the correct date time format for converesion
formatdatetime(body('Get_response_details')?['submitDate'],'yyyy-MM-ddTHH:mm:ssZ')

//Examples
convertFromUtc('DATE_FIELD','W. Europe Standard Time','dd.MM.yyyy HH:mm')
convertToUtc('DATE_FIELD','W. Europe Standard Time')

FormatDateTime

You can use this expression if you want to format a date time string either for your specific purpose or if you want to use a date for a filter query or to save it to a file or item in SharePoint.

There is a huge variety for date and time formats. You can find them here:

Standard date and time format strings - .NET

Learn how to use a standard date and time format string to define the text representation of a date and time value in .NET.

learn.microsoft.com

For SharePoint you will always need “yyyy-MM-dd”. Be careful to use the correct place holders here, as case sensitivity matters, for example when you use MM (month) or mm (minutes).

formatdatetime('2024-08-09','dd.MM.yyyy')
-> 09.08.2024
formatdatetime('2024-08-09T09:54','dd.MM.yyyy HH:mm')
-> 09.08.2024 09:54
//format into a string that works for other date time expressions
formatdatetime('DATETIME','yyyy-MM-ddTHH:mm:ssZ')

Manipulation expressions

Coalesce

SharePoint actions (or others as well) not always return columns where the value is empty. In that case your flow might fail if you try to use values from the actions response.

Solution: Coalesce will check all values you pass into it, if they are null (or empty) or contain values. It will then return the first non null value from one or more parameters like strings, arrays or objects.

How to use: Add the value (which might be empty) in your Coalesce expression. Then add other values (comma separated), that need to be checked if null (or empty). In this example the itemPrice column might be empty and there not returned by the Get item action. In that case It will automatically return the “No Price” since the text is not null.

Coalesce(outputs('Get_item')?['body/itemPrice'],'No Price')

xpath

You can access XML items, almost as easy as you can access JSON items. After you got familiar with the syntax, there is only one more thing to know. When you access arrays by their number, in XML notation the first array item starts with the number 1 (as opposed to JSON arrays at 0).

//Access the first (or only) value of the attribute
xpath(xml('XML-FILE'),'string(//ATTRIBUTE)')
//Access the first child and its attribute
xpath(xml('XML-FILE'),'string(//CHILD[1]/ATTRIBUTE)')

xpath loop in an arrays

Sadly you cannot use an apply to each with XML, unless you translate it. What you can do instead is a Do until loop by using the count operator.

//
xpath(xml('XML-FILE'),'count(//VALUE)')
xpath(xml('XML-FILE'), concat('string(//ARRAY-OBJECTNAME[','YOUR INCREMENTING VARIABLE', ']/VALUE)'))

Creating a csv with special or language specific characters

You might want to create a csv table with names, which in many languages will contain special characters like in German a,ö or ü. The “create CSV” action in Power Automate is not UTF-8 encoded by default. To fix this simply add a few UTF characters in front of your csv content. This expression then can be put into a “Create file” actions or directly into the attachments content in an “Send email” action.

concat(uriComponentToString('%EF%BB%BF'),body('Create_CSV_table'))

Miscellaneous

Dynamically insert expressions into other expressions

Example: A Microsoft Forms has Ids for each question in your form. These change for each form, unless you copy a form and don’t delete and re-add the question. Now, to keep a real application lifecycle, you should create two forms, one dev, the other prod. This will leave you with different form ids and sometimes different question ids.

So what I did: I create an environment variable as type JSON in my solution and added a simple JSON object that contains the forms id and all question ids that I need. You can now use this variable in your flow

//Example value 
formatDateTime(body('Get_response_details')?['r48a7cb8cd6e544eeb68222e8dc041e2e'],'yyyy-MM-dd')

//variable as a JSON object to parse the QuestionId
variables('FormsData')?['QuestionId']

//direct inserting it into the string
formatDateTime(body('Get_response_details')?[variables('FormsData')?['QuestionId']],'yyyy-MM-dd')

Dynamic Table Range for Excel

Your table might not be fixed to a certain amount of columns, but rather dynamic. You can fix this by using known function and provide $ symbols where it should dynamically expand to.

$A$1:$A$1
//Starts at row 7
$A$7:$A$7

Style tables created in “Create HTML Table” action

The style listed here is very simple and will format the table header with a background color, font size and font color and align the text to the left. It will also add some padding to the table it self and will alternate between white and a shade of blue in the table rows.

<style>
        table td, table th {
          padding: 3px 10px;
        }
       table tr:nth-child(even) {
          background: #D9E1F2;
	}
	table thead {
	  background: #4472C4;
	}
	table thead th {
	  font-size: 15px;
          font-weight: bold;
          color: #FFFFFF;
          text-align: left;
	}
</style>

Get the first day of a week

//If the start of the week is Monday
formatDateTime(subtractFromTime(addDays(utcNow(), 1), dayOfWeek(utcNow()), 'Day'), 'yyyy-MM-dd')

Injecting a index into an array

With a select action

FROM: range(0,length('ARRAY'))
MAP: addProperty('ARRAY'[item()],'Index',item())

Using item() in a for each loop

If you want to use properties of a current item in your loop, the designer will automatically set the correct syntax for you. That syntax looks like this:

items('For_each')?['evgSubscriberLanguage/Value']

The name of the loop action is within the name. Usually, when you rename this loop it should update accordingly in its actions, but sometimes it doesn’t. There is another syntax you can use, typing it manually. As long as you only have one loop and no further nested loops, you can use this simplified syntax:

item()?['evgSubscriberLanguage/Value']

You might notice that this syntax also comes into play when using select and filter array actions.

URL to access an app

Paste this into an email or teams message to open a app. It might make sense to have the app id available through an environment variables.

https://apps.powerapps.com/play/e/@{workflow()['tags']['environmentName']}/a/ENV-VARIABLE

Extracting the site url from manual SharePoint trigger

For a selected item returns the complete path of the selected item. With this you will only get the site url.

join(take(split(triggerBody()?['entity']?['itemUrl'],'/'),5),'/')

Workflow()

Provides a link to the current workflow. Use this in error handlers.

https://make.powerautomate.com/manage/environments/@{workflow()['tags']['environmentName']}/flows/@{workflow()['name']}/details"