Formula reference for Power Apps
Have you checked out new Microsoft Power Fx?
Formulas combine many elements. Listed below are:
- Functions take parameters, perform an operation, and return a value. For example, Sqrt(25) returns 5. Functions are modeled after Microsoft Excel functions. Some functions have side effects, such as SubmitForm, which are appropriate only in a behavior formula such as Button.OnSelect.
- Signals return information about the environment. For example, Location returns the device's current GPS coordinates. Signals don't take parameters or have side effects.
- Enumerations return a pre-defined constant value. For example, Color is an enumeration that has pre-defined values for Color.Red, Color.Blue, and so forth. Common enumerations are included here; function-specific enumerations are described with the function.
- Named operators, such as ThisItem and Self, provide access to information from within a container.
Other elements include:
Abs – Absolute value of a number.
Acceleration – Reads the acceleration sensor in your device.
Acos – Returns the arccosine of a number, in radians.
Acot – Returns the arccotangent of a number, in radians.
App – Provides information about the currently running app and control over the app's behavior.
Asin – Returns the arcsine of a number, in radians.
Assert – Evaluates to true or false in a test.
As – Names the current record in gallery, form, and record scope functions such as ForAll, With, and Sum.
AsType – Treats a record reference as a specific table type.
Atan – Returns the arctangent of a number, in radians.
Atan2 – Returns the arctangent based on an (x,y) coordinate, in radians.
Average – Calculates the average of a table expression or a set of arguments.
Back – Displays the previous screen.
Blank – Returns a blank value that can be used to insert a NULL value in a data source.
Calendar – Retrieves information about the calendar for the current locale.
Char – Translates a character code into a string.
Choices – Returns a table of the possible values for a lookup column.
ClearData – Clears a collection or all collections from an app host such as a local device.
Clock – Retrieves information about the clock for the current locale.
Coalesce – Replaces blank values while leaving non-blank values unchanged.
Collect – Creates a collection or adds data to a data source.
Color – Sets a property to a built-in color value.
ColorFade – Fades a color value.
ColorValue – Translates a CSS color name or a hex code to a color value.
Compass – Returns your compass heading.
Concat – Concatenates strings in a data source.
Concatenate – Concatenates strings.
Concurrent – Evaluates multiple formulas concurrently with one another.
Connection – Returns information about your network connection.
Count – Counts table records that contain numbers.
Cos – Returns the cosine of an angle specified in radians.
Cot – Returns the cotangent of an angle specified in radians.
CountIf – Counts table records that satisfy a condition.
CountRows – Counts table records.
DataSourceInfo – Provides information about a data source.
Date – Returns a date/time value, based on Year, Month, and Day values.
DateAdd – Adds days, months, quarters, or years to a date/time value.
DateDiff – Subtracts two date values, and shows the result in days, months, quarters, or years.
DateTimeValue – Converts a date and time string to a date/time value.
DateValue – Converts a date-only string to a date/time value.
Day – Retrieves the day portion of a date/time value.
Defaults – Returns the default values for a data source.
Degrees - Converts radians to degrees.
Distinct – Summarizes records of a table, removing duplicates.
Download – Downloads a file from the web to the local device.
DropColumns – Returns a table with one or more columns removed.
EditForm – Resets a form control for editing of an item.
EncodeUrl – Encodes special characters using URL encoding.
EndsWith – Checks whether a text string ends with another text string.
Errors – Provides error information for previous changes to a data source.
exactin – Checks if a text string is contained within another text string or table, case dependent. Also used to check if a record is in a table.
Exit – Exits the currently running app and optionally signs out the current user.
Exp - Returns e raised to a power.
Filter – Returns a filtered table based on one or more criteria.
Find – Checks whether one string appears within another and returns the location.
First – Returns the first record of a table.
FirstN – Returns the first set of records (N records) of a table.
ForAll – Calculates values and performs actions for all records of a table.
GroupBy – Returns a table with records grouped together.
GUID – Converts a GUID string to a GUID value or creates a new GUID value.
HashTags – Extracts the hashtags (#strings) from a string.
Hour – Returns the hour portion of a date/time value.
If – Returns one value if a condition is true and another value if not.
IfError - Detects errors and provides an alternative value or takes action.
in – Checks if a text string is contained within another text string or table, case independent. Also used to check if a record is in a table.
Int – Rounds down to the nearest integer.
IsEmpty – Checks for an empty table.
IsError – Checks for an error.
IsMatch – Checks a string against a pattern. Regular expressions can be used.
IsNumeric – Checks for a numeric value.
ISOWeekNum – Returns the ISO week number of a date/time value.
IsToday – Checks whether a date/time value is sometime today.
IsType – Checks whether a record reference refers to a specific table type.
JSON - Generates a JSON text string for a table, a record, or a value.
Language – Returns the language tag of the current user.
Last – Returns the last record of a table.
LastN – Returns the last set of records (N records) of a table.
Launch – Launches a webpage or a canvas app.
Left – Returns the left-most portion of a string.
Len – Returns the length of a string.
Ln – Returns the natural log.
LoadData – Loads a collection from an app host such as a local device.
Location – Returns your location as a map coordinate by using the Global Positioning System (GPS) and other information.
Log – Returns the logarithm in any base of a number.
LookUp – Looks up a single record in a table based on one or more criteria.
Lower – Converts letters in a string of text to all lowercase.
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.
Max – Maximum value of a table expression or a set of arguments.
Mid – Returns the middle portion of a string.
Min – Minimum value of a table expression or a set of arguments.
Minute – Retrieves the minute portion of a date/time value.
Mod – Returns the remainder after a dividend is divided by a divisor.
Month – Retrieves the month portion of a date/time value.
Navigate – Changes which screen is displayed.
NewForm – Resets a form control for creation of an item.
Notify – Displays a banner message to the user.
Now – Returns the current date/time value.
Param – Access parameters passed to a canvas app when launched.
Parent – Provides access to a container control's properties.
Patch – Modifies or creates a record in a data source, or merges records outside of a data source.
Pi – Returns the number π.
PlainText – Removes HTML and XML tags from a string.
Proper – Converts the first letter of each word in a string to uppercase, and converts the rest to lowercase.
Radians - Converts degrees to radians.
Rand – Returns a pseudo-random number.
ReadNFC – Reads a Near Field Communication (NFC) tag.
RecordInfo – Provides information about a record of a data source.
Refresh – Refreshes the records of a data source.
Relate – Relates records of two tables through a one-to-many or many-to-many relationship.
Remove – Removes one or more specific records from a data source.
RemoveIf – Removes records from a data source based on a condition.
RenameColumns – Renames columns of a table.
Replace – Replaces part of a string with another string, by starting position of the string.
RequestHide – Hides a SharePoint form.
Reset – Resets an input control to its default value, discarding any user changes.
ResetForm – Resets a form control for editing of an existing item.
Revert – Reloads and clears errors for the records of a data source.
RGBA – Returns a color value for a set of red, green, blue, and alpha components.
Right – Returns the right-most portion of a string.
Round – Rounds to the closest number.
RoundDown – Rounds down to the largest previous number.
RoundUp – Rounds up to the smallest next number.
SaveData – Saves a collection to an app host such as a local device.
Search – Finds records in a table that contain a string in one of their columns.
Second – Retrieves the second portion of a date/time value.
Select – Simulates a select action on a control, causing the OnSelect formula to be evaluated.
Self – Provides access to the properties of the current control.
Sequence – Generate a table of sequential numbers, useful when iterating with ForAll.
Set – Sets the value of a global variable.
SetFocus – Moves input focus to a specific control.
SetProperty – Simulates interactions with input controls.
ShowColumns – Returns a table with only selected columns.
Shuffle – Randomly reorders the records of a table.
Sin – Returns the sine of an angle specified in radians.
Sort – Returns a sorted table based on a formula.
SortByColumns – Returns a sorted table based on one or more columns.
Split – Splits a text string into a table of substrings.
Sqrt – Returns the square root of a number.
StartsWith – Checks if a text string begins with another text string.
StdevP – Returns the standard deviation of its arguments.
Substitute – Replaces part of a string with another string, by matching strings.
SubmitForm – Saves the item in a form control to the data source.
Sum – Calculates the sum of a table expression or a set of arguments.
Switch – Matches with a set of values and then evaluates a corresponding formula.
Table – Creates a temporary table.
Tan - Returns the tangent of an angle specified in radians.
Text – Converts any value and formats a number or date/time value to a string of text.
ThisItem – Returns the record for the current item in a gallery or form control.
ThisRecord – Returns the record for the current item in a record scope function, such as ForAll, With, and Sum.
Time – Returns a date/time value, based on Hour, Minute, and Second values.
TimeValue – Converts a time-only string to a date/time value.
TimeZoneOffset – Returns the difference between UTC and the user's local time in minutes.
Today – Returns the current date/time value.
Trace - Provide additional information in your test results.
Trim – Removes extra spaces from the ends and interior of a string of text.
TrimEnds – Removes extra spaces from the ends of a string of text only.
Trunc – Truncates the number to only the integer portion by removing any decimal portion.
Ungroup – Removes a grouping.
Unrelate – Unrelates records of two tables from a one-to-many or many-to-many relationship.
Update – Replaces a record in a data source.
UpdateIf – Modifies a set of records in a data source based on a condition.
Upper – Converts letters in a string of text to all uppercase.
User – Returns information about the current user.
Validate – Checks whether the value of a single column or a complete record is valid for a data source.
Value – Converts a string to a number.
VarP – Returns the variance of its arguments.
ViewForm – Resets a form control for viewing of an existing item.
Weekday – Retrieves the weekday portion of a date/time value.
WeekNum – Returns the week number of a date/time value.
With – Calculates values and performs actions for a single record, including inline records of named values.
Year – Retrieves the year portion of a date/time value.