7 Lesser Known Power Apps Functions And Why I Use Them

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:


Graphing Sine Function



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

FirstNameLastName
JimSmith
RachelAnderson
SarahGreen
JimSmith
DerekGray



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

GUIDFirstNameLastName
b6d65767-849e-4484-9f96-26b3e81e5489JimSmith
e68ed2e7-04c7-4a45-b3f9-409dec5b2587RachelAnderson
e0dd3734-edc1-49ac-97d4-b57013af4b0aSarahGreen
4d3a72b1-cb98-48c1-a0f3-18e8adae74a1JimSmith
81931a18-0922-4e66-9a34-9072ee83b1a7DerekGray



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.

Matthew Devaney

Subscribe
Notify of
guest

19 Comments
Oldest
Newest
Inline Feedbacks
View all comments
Krishna Rachakonda
Krishna Rachakonda
3 years ago

Very well explained. Loved it.

Hank Hepler
Hank Hepler
3 years ago

Thank you sir! Seems I have some refactoring to do now lol πŸ™‚

F.G
F.G
3 years ago

Hi Mat
How can I format Date in SEQUENCE Function to get “dd/mm/yyyy” please?
Tried >Format(ForAll(Sequence(7), Today() + Value),”dd/mm/yyyy”)
(No luck)

Randy Hayes
Randy Hayes
3 years ago

Looks good Matt! Well explained.

Martins
Martins
3 years ago

Thank you!

Stu R
Stu R
2 years ago

Love your guides, which helped me greatly when building my first app. Now on my second app and have turned here for advice on using the coalesce function πŸ™‚ Just thought I’d drop a little note to let you know there’s a closing parenthesis missing from your Coalesce statement (I think) after the Defaults value, in case anyone else encounters issues!

Deb A
Deb A
1 year ago

You saved me again, thank you. p.s. I love your cats! 🐈

Christopher M
Christopher M
1 year ago

I’m not for skinning any animal, much less a cat. However, I do believe there’s more than one way to feed a cat.

I’m also not the kind of guy who throws rocks at birds. Instead, I prefer to feed two birds with one seed.

Christopher M
Christopher M
1 year ago

10 out of 10 cats agree: More than one way to solve a problem works well.

Tim Jackson
Tim Jackson
1 year ago

I discovered the Patch existing/new use case of the Coalesce function last night. I was about to publish something along the lines of “Most elegant way to update or add a new record in power apps”. I thought I’d give the function a quick google first and happened on this article.

This blog post is brilliant, and I’ll take so much more away from it that COALESCE…

Paige
Paige
11 months ago

Wow! I would have never thought to animate icons in PowerApps. That’s incredible.

There are many other useful functions in here that will help me code more concisely or find new ways to solve problems! Thank you.

AerisLives
11 months ago

this is it! Coalesce! Update if found, insert if not!