Power Automate: Handy Expressions

Why?

In the Low-Code Power Platform common business logic can be “dragged & dropped” in your Microsoft Power Automate Flow. For the cases where the logic gets more complex (or if you simpy dislike drag&drop), you will have to resort to expressions.

What?

This post is will show some common copy-and-pasteable expressions from the Workflow Definition Language. Also make sure to check the GitHub Repository with references for valuable information.

How?

Important: when you see an expression starting with @ you can paste the code directly in the Value of the Action.

Otherwise you need to open the Expression tab of the Dynamic Content Pane:

TIP: the you can always paste an @{} code in the Expression tab but then you have to remove the @ and the {} 💡.

Get the Web URL from a SharePoint item

Example: I want to get the URL (maybe for the variable varWebUrl) of a SharePoint Site Collection from the triggering item in a SharePoint List:

substring(triggerBody()?['{Link}'],0,indexOf(triggerBody()?['{Link}'],'_layouts'))

Input Link = https://[ Tenant ].sharepoint.com/sites/sharepoint-site-collection/_layouts/15/listform.aspx?PageType=4&ListId=de46bd2d-2e29-4bb5-a6a4-435af1ee4e1e&ID=82&ContentTypeID=0x010060AF3D67BB2D5D47A95009E196C19A8C00B25019960BE0904188B459F2E9E7035F
Result = https://[ Tenant ].sharepoint.com/sites/sharepoint-site-collection/

Get the Web URL from a SharePoint document

Example: I want to get the URL(maybe for the variable varWebUrl) of a SharePoint Site Collection from the triggering item in a SharePoint Document Library:

substring(triggerBody()?['{Link}'], 0, indexOf(triggerBody()?['{Link}'], split(triggerBody()?['{Path}'],'/')[0]))

Input Link = https://[Tenant].sharepoint.com/sites/sharepoint-site-collection/Documents/IC0008/Contract%20IC0008%20TST0920A?d=wd857f6c88438485ca7d92ae551233f12
Result = https://[ Tenant ].sharepoint.com/sites/sharepoint-site-collection/

Get Site Collection part of Web URL

Example: I want to get a the part of the URL from varWebUrl where the site collection part is defined:

last(take(split(variables('varWebUrl'),'/'),sub(length(split(variables('varWebUrl'),'/')),1)))

Input = https://[ Tenant ].sharepoint.com/sites/sharepoint-site-collection/
Results= sharepoint-site-collection
(💡 just increase the last 1 with a different number to take previous parts)

Get a property from an array based on its index in the array

Example: I have used a Get items action on the SharePoint Connector to get a lot of SharePoint items in an array. I know the ID column of the SharePoint List Item is equal to 107 so item has an index number of 106 (remember that the index numbering in an array starts with 0).

body('Get_Items_-_SharePointList')?['Value']?[add(int(107),int(-1))]?['Text']

Input = array of 106 SharePoint items with ID‘s from 1 to 107 so it has indexes from 0 to 106
Results = Text column with string “You just got a text from an array“

Convert Time Zone from RSS Feed

Example: I want to make sure that a timestamp from a RSS feed including a timezone is converted to the correct timezone.

Because most dates are in UTC time zone by default we want to to make sure to get the correct and clean timestamp of your chosen time zone (see the Microsoft Time Zone Values for the correct index and naming):

@{convertFromUtc(items('Apply_to_each_RSSfeedItem')?['publishDate'],'W. Europe Standard Time')}

Input Timestamp = 2019-09-22 05:00:05Z
Result = 2019-09-22T07:00:05.0000000

Optionally if we want to write back the correct timestamp to a UTC value we would need to use the convertFromUtc value in a converToUtc formula again:

@{convertToUtc(outputs('Compose_ConvertFromUTC'), 'W. Europe Standard Time')}

Input Timestamp = 2019-09-22T07:00:05.0000000
Result = 2019-09-22T05:00:05.0000000Z

If we would use the convertToUtc formula directly on the first timestamp, an error like
❌ “The template language function ‘convertToUtc’ expects its second parameter to be a time zone matching the time zone indicated by the timestamp. The provided value ‘(UTC+01:00) Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna’ is not valid or does not match the timestamp” ❌
could occur.

Formate Date Time Stamp

Example: I want to convert a date / timestamp to desirable format (see the custom format possibilities for strings):

formatDateTime(outputs('Compose_Check'),'yyyy-MM-dd HH:mm')

Input Timestamp = 2019-09-22T11:56:59.1146345
Result = 2019-09-22 11:56

Get number of items from array

Some formulas of actions like Get items or Get files return a list of items in array. However when you want to get the count of the number of items or files returned and reference the List of items from the Dynamic Content you will get an error like: “The template language function ‘length’ expects its parameter to be an array or a string. The provided value is of type ‘Object’“. To prevent this error reference the Value of the Body:

length(body('Get_files_(properties_only)')?['value'])

Get Property of first Item in Array

Example: I want to get a specific FieldName of the first item from an array request:

first(body('Send_an_HTTP_request_to_SharePoint_-_GetInfo')?['d']['results'])?['FieldName']

Relate CDS records

When using the Get a record-action, we can use the following expression to get the needed notation to link in the Lookup column of the relation:

first(split(last(split(body('Get_a_record_-_ApprovingUser')?['@odata.editLink'],'')),','))

When using the List records-action, we can use the following expression to get the needed notation from the first record listed:

first(split(last(split(first(outputs('List_records_-_ApprovingUsers')?['body/value'])?['@odata.editLink'],'')),','))

Input = “@odata.editLink”: “systemusers(acbea0ad-fb09-eb11-a813-000d3a4b220c)“
Result = systemusers(acbea0ad-fb09-eb11-a813-000d3a4b220c)