7 Lesser Known Power Apps Functions And Why I Use Them

Imported reference from Matthew Devaney's blog for learning purposes. Original: https://www.matthewdevaney.com/7-lesser-known-power-apps-functions-and-why-i-use-them/. Author: Matthew Devaney.

7 Lesser Known Power Apps Functions And Why I Use Them Posted by - Matthew Devaney on - September 27, 2020 19 Comments

Power Apps has over 100 different functions and you can build awesome apps even if you only know the basics. In this article, I would like introduce you to some functions you might heard of before but you may not understand their purpose. I will show you practical ways to use the following functions in your own app: COALESCE, SIN, UNGROUP, SEQUENCE, TRACE, JSON and GUID. Table of Contents COALESCE Function SIN Function UNGROUP Function SEQUENCE Function TRACE Function JSON Function GUID Function COALESCE Function What it does:

COALESCE takes a series of values and returns the first non-blank value.

// Try this example in the Text property of a label. Coalesce(Blank(), 2 ) Output : 2 Code language: JavaScript ( javascript )

Why I use it: COALESCE is a simple way to show a default value when a variable is equal to blank . Coalesce(varSomething, "Default Text" ) Code language: JavaScript ( javascript )

It is shorter to use COALESCE than writing an IF statement with the ISBLANK function.

If(!IsBlank(varSomething), varSomething, "Default Text" ) Code language: JavaScript ( javascript )

I can also write a single PATCH function to insert a new record or update existing records instead of doing it twice.

// Patch function to insert new record or update existing record

Patch( datasource_name, Coalesce( Lookup(datasource_name, ID=some_value), Defaults(datasource_name) ), { ColumnName : "Text Value" } )

// Same Patch function without Coalesce must be written twice

If( !IsBlank(Lookup(datasource_name, ID=some_value)) Patch( datasource_name, Lookup(datasource_name, ID=some_value), { ColumnName : "Text Value" } ), Patch( datasource_name, Defaults(datasource_name), { ColumnName : "Text Value" } ) ) Code language: JavaScript ( javascript ) SIN function

The SIN function is important in trigonometry. That’s all I can remember about it from my high-school math class over one decade ago. When plotted on a chart I’m told it looks like this:

Why I use it: animation is a great way to draw an app user’s attention to an action they need to take. In the image below I want the user to add a record to a gallery. The linear animation looks lifeless and boring whereas the animation with SIN feels bouncy and fun.

I made the animation by using this code in the Y property

of the arrow icon where 275 is the starting Y-position and I want it to move up and down by 60.

275-Sin(Timer1.Value/Timer1.Duration*2*Pi())*60

You’ll also need a Timer with these properties to make it work. Autostart : true Duration : 1000 Repeat : true Code language: HTTP ( http ) UNGROUP Function

The UNGROUP function is used to undo grouping in a collection and return separate records. Code to create Output table: Ungroup(CitiesByCountry, "Cities" ) Code language: JavaScript ( javascript )

Why I use it: with a little-bit of creativity I discovered that UNGROUP can join two collections together just like a LEFT JOIN in SQL . Code to create Output table:

Ungroup( DropColumns( AddColumns(colSales

"myGroupedColumn" , Filter(colProduct, ProductID=colSales[@ProductID]) ), "ProductID ), " myGroupedColumn " ) Code language: PHP ( php )

I also like to exploit the UNGROUP function when building dropdown filters for a gallery . Along with the DISTINCT function it can create a list of unique values with a blank value on the top.

Code in the Items property of the output dropdown. Ungroup (

Table ( { MyTables : Table ({Result: Blank()})}, { MyTables : RenameColumns ( Distinct( 'Paid Time Off' , Employee),

"Result" , "Value" ) } ), " MyTables " ) Code language: CSS ( css ) SEQUENCE Function

The SEQUENCE function generates a range of numbers and outputs a single column table Sequence (10) Output : [1,2,3,4,5,6,7,8,9,10] Code language: CSS ( css )

Why I use it: combining the SEQUENCE function with the FORALL function allows me to go beyond simply creating a list of numbers but also dates, letters and many more possibilities which I have made a huge list of .

In the dropdown below I’ve allowed the user to select a date within one week from today’s date

I put this code in the Items property of the dropdown to do it. ForAll(Sequence(7), Today() + Value)

Another cool thing I can do with SEQUENCE is create a gallery with a number of blank rows custom-defined by the user.

Here’s the code I used in the Items property of the gallery. ForAll ( Sequence ( Value ( TextInput4 .Text )), Blank ()) Code language: CSS ( css ) TRACE Function

The TRACE function sends telemetery (i.e. information about how the app is being used) to Azure Application Insights where developers can monitor app performance and issues. Trace ( " Form Failed To Submit ", Trace .Severity .Error , {

User : User ().FullName, Screen: App.ActiveScreen, Time: Now () } ) Code language: CSS ( css )

Why I use it: If you don’t have any plans to try Azure Application Insights TRACE is still handy because its output is visible in Power Apps Studio’s Monitor tool. Many times I find myself using TRACE to help me debug when a variable is returning an unexpected value.

For instance, if I put this code in the OnSelect property of a button and clicked it…

Set (varNumber, 1 ); Trace( "Number: " &varNumber);

Set (varNumber, varNumber + varNumber); Trace( "Number: " &varNumber);

Set (varNumber, varNumber + varNumber); Trace( "Number: " &varNumber);

Set (varNumber, varNumber + varNumber); Trace( "Number: " &varNumber); Code language: JavaScript ( javascript )

…the TRACE information would show up inside the Monitor like this. JSON Function

The JSON function converts a table to a text string in the JavaScript Object Notation (JSON) format

Code to create the JSON from a collection:

JSON (colEmails, JSONFormat.IndentFour); Code language: JavaScript ( javascript )

Why I use it: sometimes I want to pass a collection to Power Automate to be used in a Flow. Power Automate cannot accept a collection as an input, therefore, I convert it to a JSON and pass the text string instead. Once inside Power Automate I use the Parse JSON function to interpret the object and I can loop over the result to perform an action for each record in my original collection.

For example, I can send colEmails to Power Automate as a JSON like this: 'Collection to JSON: Send Emails' .Run(

JSON (colEmails, JSONFormat.IndentFour) ); Code language: JavaScript ( javascript )

Then I can Parse the JSON and send a separate email for each record like this: GUID Function

The GUID function creates a ‘globally unique identifier’ represented as a hexadecimal text string. GUIDs are randomly generated and although it is technically possible to produce two matching GUIDS the probability is so low that its effectively zero. This is why many databases use GUIDS as a primary key (i.e. unique id). GUID()

Output: b6d65767-849e-4484-9f96-26b3e81e5489

Why I use it: when working with collections they often do not have a unique identifier. If the collection has a duplicate record it becomes difficult to tell Power Apps which one I want to target for updates. For instance, the example below has 2 records for ‘Jim Smith’. FirstName LastName Jim Smith Rachel Anderson Sarah Green Jim Smith Derek Gray

By adding a GUID I now have a way to target the specific record for ‘Jim Smith’ I want to update. GUID FirstName LastName b6d65767-849e-4484-9f96-26b3e81e5489 Jim Smith e68ed2e7-04c7-4a45-b3f9-409dec5b2587 Rachel Anderson e0dd3734-edc1-49ac-97d4-b57013af4b0a Sarah Green 4d3a72b1-cb98-48c1-a0f3-18e8adae74a1 Jim Smith 81931a18-0922-4e66-9a34-9072ee83b1a7 Derek Gray Here’s how I added the GUID: AddColumns(colNames, "GUID" , GUID()) Code language: JavaScript ( javascript ) Did You Enjoy This Article? 😺

Subscribe to get new Copilot Studio articles sent to your inbox each week for FREE Enter your email address Sign Me Up Questions?

If you have any questions or feedback about 7 Lesser Known Power Apps Functions And Why I Use Them please leave a message in the comments section below. You can post using your email address and are not required to create an account to join the discussion. COALESCE function GUID function JSON function SEQUENCE function SIN function TRACE function UNGROUP function Matthew Devaney M365 Copilot Power Apps

How To Create Copilot Custom UI Widgets In Power Apps