The Tech Platform Presents Talks with Aroh Shukla on Power Apps (Basics on Formulas, Search, Filter, Patch function).
Subscribe us at www.thetechplatform.com for more articles and live events.
Below we have the links for Youtube and Facebook Video:
Understanding Power Apps Formula
Overview:
Each Control can have many properties
Formulas make up the definition for the
Control properties in your app
Configure your canvas app with formulas that not only calculate values and perform other tasks (as they do in Excel) but also respond to user input (as an app requires).
In Excel, you build formulas that, for example, populate cells and create tables and charts.
In Power Apps, you build similar formulas as you configure controls instead of cells. In addition, you build formulas that apply specifically to apps instead of spreadsheets.
Different From Traditional Coding - Think Like Excel
PowerApps Formulas are very similar to Excel Formulas
Lots of small pieces of code rather than complex scripts.
As things change in the app formulas are automatically recalculated.
Formulas can reference other properties like referencing other cells in a spreadsheet
Formula syntax
As you type a formula in the formula bar, different syntax elements appear in different colors to improve readability and help you understand long formulas. Here is the color code list in Power Apps.
Formula reference for 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.
Click on the below link to read the properties in details.
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 entity 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
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.
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.
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.
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
H
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.
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.
IsToday – Checks whether a date/time value is sometime today.
IsType – Checks whether a record reference refers to a specific entity type.
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 a local device's storage.
Location – Returns your location as a map coordinate by using the Global Positioning System (GPS) and other information.
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.
O
P
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.
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.
Refresh – Refreshes the records of a data source.
Relate – Relates records of two entities 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 a local device's storage.
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/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.
U
Ungroup – Removes a grouping.
Unrelate – Unrelates records of two entities 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.
V
W
Y
Year – Retrieves the year portion of a date/time value
Comments