Every Power Apps formula is built from functions. Need to show one record from a SharePoint list, add 30 days to a due date, or check whether a text input is empty before you save? There’s a function for it, and once you know the ones you’ll use every week, most canvas app problems get a lot smaller.
This page lists all 201 functions that Microsoft documents for canvas apps, grouped by what they do. Select a function name to open its full tutorial with the syntax, real examples built in a canvas app, and the mistakes to avoid. New function tutorials are added every day, so the list of links keeps growing.
Jump to a category
- Text functions (27)
- Logical and information functions (10)
- Table functions (filter, sort, and shape data) (22)
- Aggregate and count functions (10)
- Data source and collection functions (24)
- Variable functions (2)
- Navigation and app functions (18)
- Form and control functions (10)
- Date and time functions (25)
- Conversion and JSON functions (12)
- Error handling and testing functions (6)
- Math and trigonometry functions (26)
- Color functions (3)
- AI functions (6)
How to use a function in Power Apps
Quick steps: select a control (a label, button, or gallery), pick the property in the dropdown to the left of the formula bar (for example Text, Items, or OnSelect), type the formula in the formula bar, and select Play (or hold Alt and select a button) to test it.
Functions can be nested, so the result of one becomes the input of another. This formula on a label’s Text property shows a hint while the text input is empty and the trimmed, uppercase title once the user types:
If(IsBlank(txtTitle.Text), "Enter a title", Upper(Trim(txtTitle.Text)))
Text values go in double quotes, and you don’t start a formula with an equal sign. Functions that change something, such as Navigate, Patch, Set, and Notify, are behavior functions: they only work in behavior properties like a button’s OnSelect. To run several of them in a row, separate them with a semicolon.
Text functions
Text functions cut, search, join, and clean strings: the first few characters of a ticket number, a full name from two columns, or the hashtags in a comment. You’ll use them in labels, text inputs, and SharePoint text columns.
| Function | What it does |
|---|---|
| Text | Converts any value and formats a number or date/time value to a string of text. |
| Left | Returns the left-most portion of a string. |
| Mid | Returns the middle portion of a string. |
| Right | Returns the right-most portion of a string. |
| Substitute | Replaces part of a string with another string, by matching strings. |
| IsMatch | Checks a string against a pattern. Regular expressions can be used. |
| Match | Extracts a substring based on a pattern. Regular expressions can be used. |
| MatchAll | Extracts multiple substrings based on a pattern. Regular expressions can be used. |
| Char | Translates a character code into a string. |
| UniChar | Translates a Unicode code into a string. |
| TrimEnds | Removes extra spaces from the ends of a string of text only. |
| EncodeUrl | Encodes special characters using URL encoding. |
| EncodeHTML | Encodes characters that need to be escaped to be used in an HTML context. |
| PlainText | Removes HTML and XML tags from a string. |
| HashTags | Extracts the hashtags (#strings) from a string. |
| Split | Splits a text string into a table of substrings. |
| Replace | Replaces part of a string with another string, by starting position of the string. |
| StartsWith | Checks if a text string begins with another text string. |
| Trim | Removes extra spaces from the ends and interior of a string of text. |
| Lower | Converts letters in a string of text to all lowercase. |
| Find | Checks whether one string appears within another and returns the location. |
| Len | Returns the length of a string. |
| Concatenate | Concatenates strings. |
| Concat | Joins a text value from every record of a table (or collection) into one string. |
| Upper | Converts letters in a string of text to all uppercase. |
| Proper | Converts the first letter of each word in a string to uppercase, and converts the rest to lowercase. |
| EndsWith | Checks whether a text string ends with another text string. |
Logical and information functions
Logical functions return true or false, or pick a value based on a condition. They drive the Visible, DisplayMode, and Fill properties, and they check whether a value is blank before you save it.
| Function | What it does |
|---|---|
| Switch | Matches with a set of values and then evaluates a corresponding formula. |
| Coalesce | Replaces blank values while leaving non-blank values unchanged. |
| And | Boolean logic AND. Returns true if all arguments are true. You can also use the && operator. |
| Or | Boolean logic OR. Returns true if any of its arguments are true. You can also use the || operator. |
| Not | Boolean logic NOT. Returns true if its argument is false, and returns false if its argument is true. You can also use the ! operator. |
| Blank | Returns a blank value you can use to insert a NULL value in a data source. |
| IsNumeric | Checks for a numeric value. |
| If | Returns one value if a condition is true and another value if not. |
| IsBlank | Checks for a blank value. |
| IsEmpty | Checks for an empty table. |
Table functions (filter, sort, and shape data)
Table functions work on a data source, a collection, or a table you build in the formula. Filter, sort, and reshape rows for a gallery, or grab one record by position or condition. Watch the delegation notes in each tutorial. For a deeper guide, see Filter vs Search vs LookUp in Power Apps.
| Function | What it does |
|---|---|
| LookUp | Looks up a single record in a table based on one or more criteria. |
| With | Calculates values and performs actions for a single record, including inline records of named values. |
| Index | Returns a record from a table based on ordered position. |
| DropColumns | Returns a table with one or more columns removed. |
| RenameColumns | Renames columns of a table. |
| ShowColumns | Returns a table with only selected columns. |
| Shuffle | Randomly reorders the records of a table. |
| Search | Finds records in a table that contain a string in one of their columns. |
| GroupBy | Returns a table with records grouped together. |
| Sort | Returns a sorted table based on a formula. |
| ForAll | Calculates values and performs actions for all records of a table. |
| Table | Creates a temporary table. |
| AddColumns | Returns a table with additional columns. |
| First | Returns the first record of a table. |
| Filter | Returns a filtered table based on one or more criteria. |
| Sequence | Generate a table of sequential numbers, useful when iterating with ForAll. |
| Distinct | Summarizes records of a table, removing duplicates. |
| SortByColumns | Returns a sorted table based on one or more columns. |
| FirstN | Returns the first set of records (N records) of a table. |
| Last | Returns the last record of a table. |
| LastN | Returns the last set of records (N records) of a table. |
| Ungroup | Removes a grouping. |
Aggregate and count functions
Aggregate functions summarize a column into one number: totals, averages, the highest or lowest value, and counts of rows that match a condition.
| Function | What it does |
|---|---|
| Max | Maximum value of a table expression or a set of arguments. |
| Min | Minimum value of a table expression or a set of arguments. |
| Average | Calculates the average of a table expression or a set of arguments. |
| StdevP | Returns the standard deviation of its arguments. |
| VarP | Returns the variance of its arguments. |
| Sum | Calculates the sum of a table expression or a set of arguments. |
| CountRows | Counts table records. |
| CountIf | Counts table records that satisfy a condition. |
| Count | Counts table records that contain numbers. |
| CountA | Counts table records that aren’t empty. |
Data source and collection functions
These functions change data: create a collection, add or update records in a SharePoint list or Dataverse table, remove rows, and check what went wrong when a save fails. For a deeper guide, see Power Apps Collection.
| Function | What it does |
|---|---|
| Collect | Creates a collection or adds data to a data source. |
| ClearCollect | Deletes all data from a collection and then adds a set of records. |
| Clear | Deletes all data from a collection. |
| Errors | Provides error information for previous changes to a data source. |
| Defaults | Returns the default values for a data source. |
| Validate | Checks whether the value of a single column or a complete record is valid for a data source. |
| Revert | Reloads and clears errors for the records of a data source. |
| Relate | Relates records of two tables through a one-to-many or many-to-many relationship. |
| DataSourceInfo | Provides information about a data source. |
| RecordInfo | Provides information about a record of a data source. |
| AsType | Treats a record reference as a specific table type. |
| IsType | Checks whether a record reference refers to a specific table type. |
| SaveData | Saves a collection to an app host such as a local device. |
| LoadData | Loads a collection from an app host such as a local device. |
| ClearData | Clears a collection or all collections from an app host such as a local device. |
| Dataverse.Unmask | Requests the unmasked value of a secured column for a single Dataverse record. |
| Choices | Returns a table of the possible values for a lookup column. |
| Patch | Modifies or creates a record in a data source, or merges records outside of a data source. |
| Refresh | Refreshes the records of a data source. |
| Update | Replaces a record in a data source. |
| Remove | Removes one or more specific records from a data source. |
| RemoveIf | Removes records from a data source based on a condition. |
| UpdateIf | Modifies a set of records in a data source based on a condition. |
| Unrelate | Unrelates records of two tables from a one-to-many or many-to-many relationship. |
Variable functions
Variables hold a value while the app runs. Set creates a global variable for the whole app, and UpdateContext creates a context variable for one screen. For a deeper guide, see Power Apps Global vs Context Variables.
| Function | What it does |
|---|---|
| UpdateContext | Sets the value of one or more context variables of the current screen. |
| Set | Sets the value of a global variable. |
Navigation and app functions
These functions control the app itself: move between screens, open a URL, show a message, ask for confirmation, download or print, and read who the current user is.
| Function | What it does |
|---|---|
| Back | Returns to the screen that was displayed before. |
| User | Returns information about the current user. |
| Exit | Exits the currently running app and optionally signs out the current user. |
| Concurrent | Evaluates multiple formulas concurrently with one another. |
| Export contents from the current screen to an object for use in multiple scenarios. | |
| Language | Returns the language tag of the current user. |
| ShowHostInfo | Displays information to the user in the app. |
| Enable | Turns on a device signal, such as Location, so the app can read it. |
| Disable | Turns off a device signal, such as Location, to save battery. |
| ReadNFC | Reads a Near Field Communication (NFC) tag. |
| Navigate | Changes the screen that’s displayed, with an optional transition. |
| Notify | Displays a banner message to the user. |
| Confirm | Shows a confirmation dialog box and returns whether the user confirmed. |
| Download | Downloads a file from the web to the local device. |
| Opens the current screen in the default browser print dialog. | |
| Copy | Copies text to the clipboard on the device where the app is running. |
| Launch | Launches a webpage or a canvas app. |
| Param | Access parameters passed to a canvas app when launched. |
Form and control functions
Form functions save or reset an Edit form and switch it between new, edit, and view mode. Control functions reset an input, move the cursor, or run another control’s OnSelect.
| Function | What it does |
|---|---|
| Reset | Resets an input control to its default value, discarding any user changes. |
| ResetForm | Resets a form control for editing of an existing item. |
| NewForm | Resets a form control for creation of an item. |
| EditForm | Resets a form control for editing of an item. |
| Select | Runs the OnSelect formula of another control, as if the user selected it. |
| SetFocus | Moves input focus to a specific control. |
| ViewForm | Resets a form control for viewing of an existing item. |
| RequestHide | Hides a SharePoint form. |
| SetProperty | Simulates interactions with input controls. |
| SubmitForm | Saves the item in a form control to the data source. |
Date and time functions
Date and time functions return today’s date, add or subtract days and months, find the difference between two dates, and pull out the year, month, or hour. Power Apps shows dates in the user’s local time zone.
| Function | What it does |
|---|---|
| Year | Retrieves the year portion of a date/time value. |
| Month | Retrieves the month portion of a date/time value. |
| Day | Retrieves the day portion of a date/time value. |
| DateTime | Returns a date/time value, based on both date and time components. |
| Date | Returns a date/time value, based on Year, Month, and Day values. |
| Time | Returns a date/time value, based on Hour, Minute, and Second values. |
| TimeValue | Converts a time-only string to a date/time value. |
| DateTimeValue | Converts a date and time string to a date/time value. |
| EOMonth | Adds or subtracts months to a date, returning the last day of that month. |
| EDate | Adds or subtracts months to a date, without changing the day of the month. |
| Hour | Returns the hour portion of a date/time value. |
| Minute | Retrieves the minute portion of a date/time value. |
| Second | Retrieves the second portion of a date/time value. |
| WeekNum | Returns the week number of a date/time value. |
| ISOWeekNum | Returns the ISO week number of a date/time value. |
| TimeZoneOffset | Returns the difference between UTC and the user’s local time in minutes. |
| Clock | Retrieves information about the clock for the current locale. |
| Calendar | Retrieves information about the calendar for the current locale. |
| Now | Returns the current date/time value in the user’s time zone. |
| DateDiff | Subtracts two date values, and shows the result in days, months, quarters, or years. |
| DateValue | Converts a date-only string to a date/time value. |
| DateAdd | Adds days, months, quarters, or years to a date/time value. |
| Weekday | Retrieves the weekday portion of a date/time value. |
| Today | Returns the current date-only value. |
| IsToday | Checks whether a date/time value is sometime today in the user’s time zone. |
Conversion and JSON functions
Conversion functions change a value from one type to another: text to number, text to Boolean, a GUID string to a GUID, and a JSON string to values you can use in the app.
| Function | What it does |
|---|---|
| Float | Converts a string to a Float value. |
| Boolean | Converts a text string, number, or dynamic value to a Boolean value. |
| JSON | Generates a JSON text string for a table, a record, or a value. |
| Column | Reads the value of a named column from a dynamic (untyped) object, such as parsed JSON. |
| ColumnNames | Returns the column names of a dynamic (untyped) object, such as parsed JSON. |
| Dec2Hex | Convert a number to a hexadecimal text string. |
| Hex2Dec | Convert a hexadecimal text string to a number. |
| Type | Defines a user defined type. |
| RecordOf | Extracts the type of a record from a table. |
| Value | Converts a text string to a number. |
| GUID | Converts a GUID string to a GUID value or creates a new GUID value. |
| ParseJSON | Converts JSON document represented as text to a Dynamic value value. |
Error handling and testing functions
Error functions catch a failed formula and show a friendly message instead, create your own errors, and check whether a value is an error. For a deeper guide, see Power Apps Exception Handling.
| Function | What it does |
|---|---|
| IfError | Detects errors and provides an alternative value or takes action. |
| Error | Create a custom error or pass through an error. |
| IsError | Checks for an error. |
| IsBlankOrError | Checks for a blank value or error. |
| Trace | Provide additional information in your test results. |
| Assert | Evaluates to true or false in a test. |
Math and trigonometry functions
Math functions round numbers, return remainders and absolute values, generate random numbers, and handle powers, logarithms, and trigonometry.
| Function | What it does |
|---|---|
| Round | Rounds to the closest number. |
| RoundUp | Rounds up to the smallest next number. |
| RoundDown | Rounds down to the largest previous number. |
| Int | Rounds down to the nearest integer. |
| Trunc | Truncates the number to only the integer portion by removing any decimal portion. |
| Abs | Returns the absolute value of a number. |
| Power | Returns a number raised to a power. You can also use the ^ operator. |
| Sqrt | Returns the square root of a number. |
| Rand | Returns a pseudo-random number between 0 and 1. |
| RandBetween | Returns a pseudo-random number between two numbers. |
| Exp | Returns e raised to a power. |
| Ln | Returns the natural log. |
| Log | Returns the logarithm in any base of a number. |
| Pi | Returns the number π. |
| Sin | Returns the sine of an angle specified in radians. |
| Cos | Returns the cosine of an angle specified in radians. |
| Tan | Returns the tangent of an angle specified in radians. |
| Cot | Returns the cotangent of an angle specified in radians. |
| Asin | Returns the arcsine of a number, in radians. |
| Acos | Returns the arccosine of a number, in radians. |
| Atan | Returns the arctangent of a number, in radians. |
| Atan2 | Returns the arctangent based on an (x,y) coordinate, in radians. |
| Acot | Returns the arccotangent of a number, in radians. |
| Degrees | Converts radians to degrees. |
| Radians | Converts degrees to radians. |
| Mod | Returns the remainder after a dividend is divided by a divisor. |
Color functions
Color functions turn a hex code or CSS color name into a color, build a color from red, green, blue, and alpha values, and make a color lighter or darker.
| Function | What it does |
|---|---|
| ColorValue | Translates a CSS color name or a hex code to a color value. |
| ColorFade | Fades a color value. |
| RGBA | Returns a color value for a set of red, green, blue, and alpha components. |
AI functions
AI functions use AI Builder prompts to classify, summarize, translate, or reply to text right inside a formula. They need AI Builder capacity in your environment.
| Function | What it does |
|---|---|
| AIClassify | Classifies text into one or more categories. |
| AIExtract | Extracts specified entities like registration numbers, phone numbers, or people’s names. |
| AIReply | Drafts a reply to the message that you provide. |
| AISentiment | Detects the sentiment of the text that you provide. |
| AISummarize | Summarizes the text that you provide. |
| AITranslate | Translates text from one language to another. |
Frequently Asked Questions
What is the difference between a function and a formula in Power Apps?
A function is one named operation, such as Filter or DateAdd, that takes arguments and returns a value. A formula is everything you type in a property, and it can combine several functions, operators, and control values, for example Text(DateAdd(Today(), 7), "mm/dd/yyyy").
Are Power Apps functions the same as Excel functions?
Many look the same, because Power Fx, the formula language of Power Apps, borrows heavily from Excel formulas. Functions such as If, Sum, Left, and Round work much like they do in Excel. Power Apps adds functions that Excel doesn’t have, such as Navigate, Patch, and Collect, and it works with tables and records instead of cell ranges.
What is a behavior function in Power Apps?
A behavior function changes the state of the app, for example Navigate, Set, Collect, Patch, or Notify. You can use behavior functions only in behavior properties such as OnSelect, OnChange, or OnVisible. Other functions can go anywhere and recalculate automatically when their inputs change.