By: Hristo Hristov | Updated: 2021-12-16 | Comments | Related: > Power Apps
Problem
In previous tips we have shown numerous essential Power Apps functions such as Patch, Filter, LookUp, among others. All of them should be very close to the heart of the app maker. What are some other useful Power Apps functions?
Solution
Follow this tip for advice on 5 useful Power Apps functions that you may have not come across yet.
Power Apps Assert Function
With this function, the Power Apps formula language gets just a bit closer to a typical scripting language. Assert, however, is only applicable while working with the Test studio in Power Apps. So, you can’t bind it to a regular control property. In a test case, you can use it to check for a value, data type or if a record has been added to the database. You must go to the Tests screen by clicking on the last icon on the left menu bar:
Once inside the Test studio, you can configure a check that verifies if a text input contains a certain text:
This example is hardcoded and will return Success every time because I added the correct value to the default property of the text input ("Correct input").
Power Apps Print Function
This one should be self-explanatory. Very helpful and at first Power Apps was not endowed by it, which comes to show that the product does evolve. Print evokes the browsers print dialog allowing you to fit one of your app’s screens to a page and print it out or export it as a PDF. The Print function, however, does not work on mobile devices – it works only in the browser. This is what happens when I press a button that has Print on its OnSelect:
From here I can choose how to print this screen.
Power Apps DataSourceInfo Function
Here is one function that can help the maker pre-validate inputs prior to the user clicking the submit button. The syntax is very straightforward: DataSourceInfo(DataSource, Information, ColumnName). For example, here is how to check if a certain column called "Name" is required. The data source name is called Categories which is a SQL table already connected to the app:
The output is boolean, depending on the outcome. In this case the column was previously set to NOT NULL in the database.
The second argument to the DateSourceInfo function can be one of several enum values Power Apps makes available. I will group these logically and in ascending order of subjective usefulness:
- AllowedValues, CreatePermission, DeletePermission, EditPermission, ReadPermission: all of these are meant to enable the maker to check the current user’s permission against a data source such as an SQL or a Dataverse table. All of them return a boolean. It must be a complex app with a set of data sources that are perhaps used dynamically, and you never know how much privileges the user does have for any of them. However, the output from DataSourceInfo with one of these parameters can also be used as a reference for the users themselves.
- DisplayName, MaxLength, MaxValue, MinValue, Required: all of these are pretty useful in implementing dynamic checks and validation in your app. Instead of hardcoding the expected length of an input, you can run a check on the go and ensure the value will be accepted. They all return a boolean output too, making it easy to incorporate them in an if-statement. After that, you display a correct feedback message if needed.
Power Apps ShowColumns Function
This one is Power Apps’ own version of the SELECT statement in T-SQL. It allows you to select only certain columns from the underlying table. This can potentially optimize performance (it is also generally considered a good practice in SQL too). For example, if a have a data source called Categories, I can do the following:
In this case, I am selecting just two of the many columns my table has – Name and Id, and I am passing the resulting table to the Items property of a gallery. It is important to note that ShowColumns does not modify the original table. It produces an in-memory result set based on the original table. Using ShowColumns will ensure you are working only with the relevant columns. If I wanted to populate one of the labels in my gallery based on the example above, I would have a choice between only the selected columns (Name and Id, IsSelected is a built-in property):
It is fair to mention DropColumns in this context too. Conversely to ShowColumns, DropColumns will exclude the stated columns from the table. So, if I did this:
then I would have a much broader choice of columns for my label inside the gallery, except for the Name and Id columns (as you can observe by the scrollbar in the dropdown):
Unfortunately, neither ShowColumns nor DropColumns support delegation. This limits you to at most 2000 records that can be returned.
Power Apps CountRows and CountIf Functions
I am grouping these two together because scenarios for their usage are often interrelated. The CountRows function counts all records in a table and returns a number. CountIf counts all rows that correspond to a certain condition. Both are useful when you want to provide some feedback to the user, e.g., count all rows in a table created by a certain user:
CountIf(Categories, CreatedBy = User().FullName)
Beware that neither CountRows nor CountIf support delegation with SQL. They do support delegation with Dataverse tables only. If you use a nested Filter function inside CountRows, then there is a hard limit of 50,000 records. When you work with CountRows with no filter, then there is no limit, as Dataverse keeps a cached count of the number of rows available in any table.
Bonus: Power Apps IsMatch Function
This is a helpful function to validate the format of the input to a text input control. Let’s say that for a certain field, you want your users to input a serial number only in the format XXXXX-YYYYY (i.e., 5 digits followed by a hyphen, followed by another five digits). What you could do is bind an instance of the IsMatch function to the BorderColor property and turn the border to red in case the user input is in a different format:
The advantage here is that as soon as the function validates the input of the
user, it will return true. In such a case, the border
color will go back to normal instantly. IsMatch can (and should)
be used for a submit button or SubmitForm function so that it will
be impossible to transmit wrong or wrongly formatted data to the data source.
There you go – five plus one functions you should be aware of in Power Apps. Knowing these should help you create better apps!
Next Steps
Check the official documentation for more information on how to utilize each function:
About the author
Hristo Hristov is a Data Scientist and Power Platform engineer with more than 12 years of experience. Between 2009 and 2016 he was a web engineering consultant working on projects for local and international clients. Since 2017, he has been working for Atlas Copco Airpower in Flanders, Belgium where he has tackled successfully multiple end-to-end digital transformation challenges. His focus is delivering advanced solutions in the analytics domain with predominantly Azure cloud technologies and Python. Hristo's real passion is predictive analytics and statistical analysis. He holds a masters degree in Data Science and multiple Microsoft certifications covering SQL Server, Power BI, Azure Data Factory and related technologies.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2021-12-16
