Power Automate Gymnastics Reference Guide

Power Platform Tip of The Day presents: Power Automate expressions you didn’t know you needed until you found them. Everyone keeps a stash of useful Power Automate expressions in Notepad++, VS Code, EMacs, vi, or sticky notes if you don’t know what any of the above mean. The list below is a curated collection of expressions people use over and over again without blinking or crying “code”.

It doesn’t count as code if you copy and paste

Amey “ABC” Holden

Contributors: Amey “ABC” Holden, Antti Pajunen, Matt Collins-Jones.

Have the favorite expression of your own? Drop it in the comments or send it straight to jar@crmtipoftheday.com

List, Array, and Loop Walk Into A Bar

First items contact ID from List Rows

first(outputs('List_Rows_Action_Name')?['body/value'])?['contactid']

First items OData ID (For UriHost()) from List Rows

first(outputs('List_Rows_Action_Name')?['body/value'])?['@odata.id']

List record action returned no results – output = true/false

empty(body('List_Rows_Action_Name')?['value'])

Choose a certain value from an HTTP request instead of using Parse JSON

body('Invoke_an_HTTP_request')?['mail']

Get the first result for an array

first(output('array'))

output('array')?[0]

Get the field from the first result of an array

output('array')?[0]?['FirstName']

Check how many results are returned

length(output('array'))

Check if array is blank

empty(output('array'))

Skip lines for array

Skip(('array'),6)

Split tab delimiter file into an array

split(outputs('Get_File_from_SharePoint')?['body'], decodeUriComponent('%09'))

Split Array by carriage return

split(string(item()), decodeUriComponent('%0A'))

Conditions

Go through this list of stuff and return the first non-blank value

coalesce(
   outputs('Get_Row_Action')?['body/description'], 
   outputs('Get_Row_Action')?['body/name'], 
   'Default value'
)

Check if something is blank and provide a different value, else use the first value

if(
    empty(variables('varEmail')),
        'foobar@gmail.com',
        variables('varEmail')    
)

Check if something is blank and provide a different value, else use the first value (Mk II)

coalesce(variables('varEmail'),  'foobar@gmail.com')

If Empty Condition

if(empty(outputs('Get_Row_Action')?['body/industrycode']), 'Yes', 'No')

If Not Equal Condition

if(not(equals(outputs('Get_Row_Action')?['body/industrycode'], 1)), 'Yes', 'No')

If Equals Condition

if(equals(outputs('Get_Row_Action')?['body/industrycode'], 1), 'Yes', 'No')

Set the owner correctly regardless if it’s a user or a team

concat(
  outputs('Get_Row_Action')?['body/_ownerid_value@Microsoft.Dynamics.CRM.lookuplogicalname'],
  's/',
  outputs('Get_Row_Action')?['body/_ownerid_value']
)

If empty do nothing, otherwise set the lookup (avoids failures when lookup value not found)
(Table is actually called lookup_table but in their wisdom designers of OData specifications introduced plural so it’s lookup_tables)

if(empty(first(outputs('Get_Rows_Action')?['body'])?['GUID_Column']), 
    null,
    concat(
      'lookup_tables(',
      first(outputs('Get_Rows_Action')?['body'])?['GUID_Column'],
      ')'
    )
)

If customer lookup is to an account, set the lookup, otherwise do nothing

if(equals(
  triggerOutputs()?['body/_customerid_value@Microsoft.Dynamics.CRM.lookuplogicalname'],
  'account'), 
    concat('accounts(',triggerOutputs()?['body/_customerid_value'],')'), 
    null
)

If Equals many conditions

if(
  and(
    equals(triggerOutputs()?['body/firstname'], 'Mike'), 
    equals(triggerOutputs()?['body/lastname'], 'Ipsum')
  ), 
'Yes', 'No')

Use with the switch case value, so if its blank, your flow doesn’t fail

coalesce(triggerBody()?['Choice']?['Value'],'Unknown')

Data Flexing Versing

Check two string values are exact

toLower(item()?['matt_email'])

Remove whitespace in string

replace(triggerOutputs()?['body/productnumber'], ' ','')

Line breaks/new lines

decodeUriComponent('%0A')
decodeUriComponent('%0D')

Remove last character of string

substring(outputs('Compose_3'),0,sub(length(outputs('Compose_3')),1))

TimeZone Conversion for AUS

convertTimeZone(utcNow(),'UTC','W. Australia Standard Time')
convertTimeZone(utcNow(),'UTC','AUS Eastern Standard Time')

Date/Time Formatting
https://docs.microsoft.com/dotnet/standard/base-types/standard-date-and-time-format-strings

Extracting a word from a sentence

first(split(last(split(outputs('Compose_random_string'), 'word ')), ' is'))

Dataverse Email activity parties with lookup or email address (‘unresolved recipient’)
(2 = To 1 = From)

[
  {   
    "participationtypemask": 2,    
    "partyid@odata.bind": "systemuser(E1AC0A77-C900-EC11-94EF-000D3ACC597E)"  
  },  
  {    
    "participationtypemask": 1,
    "addressused": "mailto:blah@email.com"  
  }
]

Words not GUIDS

Expand: Get Contact first name from customer lookup

customerid_contact($select=firstname)

Get Choice Label from List Rows Action

item()?['choiceName@OData.Commuity.Display.V1.FormattedValue']

Get Choice Labels from Get A Row Action

body('GetARow')?['choiceName@OData.Commuity.Display.V1.FormattedValue']

Name of lookup rather than GUID

@OData.Community.Display.V1.FormattedValue

e.g. 

triggerOutputs()?['body/_customerid_value@OData.Community.Display.V1.FormattedValue'] 
  
  => Joe Bloggs 
        not 82373-43949-3948394-298424

Table logical name/type e.g. customer -> contact/account or owner -> team/systemuser

@Microsoft.Dynamics.CRM.lookuplogicalname

e.g. 

triggerOutputs()?['body/_customerid_value@Microsoft.Dynamics.CRM.lookuplogicalname'] 

  => account OR contact

Expand: Get email address of owner and the person who last modified the record

owninguser($select=internalemailaddress),modifiedby($select=internalemailaddress)

Filtering

Trigger Filter expression

firstname eq 'Mike' and lastname eq 'Ipsum'

Filter based on a lookup

_logicalnamehere_value eq guidHere

Filter based on a record’s GUID

entityPrimaryKey eq guidHere

Filter based on a string 

String inside single quotes, e.g.

firstname eq 'Mike' 

Filter based on an integer (e.g. an option set) 

Integer without single quotes, e.g. 

preferredcontactmethodcode eq 1

OData filter for lookups – send a notification, about record update/assign, so long as the owner is not the same as the person who did it

( _ownerid_value ne  _modifiedby_value)
( _ownerid_value ne _modifiedonbehalfby_value)