7 Lesser Known Power Apps Functions And Why I Use Them

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

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

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

If(!IsBlank(varSomething), varSomething, "Default Text")

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"}
    )
)

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

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

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

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

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]

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

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()
    }
)

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

…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);

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

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

By adding a GUID I now have a way to target the specific record for ‘Jim Smith’ I want to update.

Here’s how I added the GUID:

AddColumns(colNames, "GUID", GUID())

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.