Power Fx formula reference for Power Apps

Reference

Power Fx is the low-code language that will be used across Microsoft Power Platform. 

Microsoft Power Fx is the new name for the formula language for canvas apps in Power Apps.

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:

• Operators and identifiers
• Controls and their properties
• Data types

A

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.

AddColumns – Returns a table with columns added.

And – Boolean logic AND. Returns true if all arguments are true. You can also use the && operator.

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.

B

Back – Displays the previous screen.

Blank – Returns a blank value that can be used to insert a NULL value in a data source.

Boolean – Converts a text string, number, or untyped value to a Boolean value.

C

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.

Clear – Deletes all data from a collection.

ClearCollect – Deletes all data from a collection and then adds a set of records.

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.

CountA – Counts table records that aren't empty.

CountIf – Counts table records that satisfy a condition.

CountRows – Counts table records.

D

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.

Disable – Disables a signal, such as Location for reading the GPS.

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.

E

EditForm – Resets a form control for editing of an item.

Enable – Enables a signal, such as Location for reading the GPS.

EncodeUrl – Encodes special characters using URL encoding.

EndsWith – Checks whether a text string ends with another text string.

Error – Create a custom error or pass throguh an error.

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.

F

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.

G

GroupBy – Returns a table with records grouped together.

GUID – Converts a GUID string to a GUID value or creates a new GUID value.

H

HashTags – Extracts the hashtags (#strings) from a string.

Hour – Returns the hour portion of a date/time value.

I

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.

Index – Returns a record from a table based on ordered position.

Int – Rounds down to the nearest integer.

IsBlank – Checks for a blank value.

IsBlankOrError – Checks for a blank value or error.

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 in the user's time zone.

IsType – Checks whether a record reference refers to a specific table type.

IsUTCToday – Checks whether a date/time value is sometime today in Coordinated Universal Time (UTC).

J

JSON - Generates a JSON text string for a table, a record, or a value.

L

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.

M

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.

N

Navigate – Changes which screen is displayed.

NewForm – Resets a form control for creation of an item.

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.

Notify – Displays a banner message to the user.

Now – Returns the current date/time value in the user's time zone.

O

Or – Boolean logic OR. Returns true if any of its arguments are true. You can also use the || operator.

P

Param – Access parameters passed to a canvas app when launched.

Parent – Provides access to a container control's properties.

ParseJSON – Converts JSON document represented as text to an Untyped object value.

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.

Power – Returns a number raised to a power. You can also use the ^ operator.

Proper – Converts the first letter of each word in a string to uppercase, and converts the rest to lowercase.

R

Radians - Converts degrees to radians.

Rand – Returns a pseudo-random number between 0 and 1.

RandBetween – Returns a pseudo-random number between two numbers.

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.

S

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.

T

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

U

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.

UpdateContext – Sets the value of one or more context variables of the current screen.

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.

UTCNow – Returns the current date/time value in Coordinated Universal Time (UTC).

UTCToday – Returns the current date-only value in Coordinated Universal Time (UTC).

V

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.

W

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.

Y

Year – Retrieves the year portion of a date/time value.