📄 18 Categories
⚡ 150+ Functions
⭐ 40+ Interview Hints
🆕 12 Pattern Recipes
Text Functions
String Inspection & Extraction
LenINTLen(Text) → NumberEx: Len("Power Fx") → 8Character count of a string.⭐ Len is for strings; CountRows is for tables — frequently confused in interviews.
FindFind(FindText, WithinText [,StartNum]) → NumberEx: Find("@", Email.Text) → 1-based position of @; Blank if not foundReturns 1-based position of a substring within text. Returns Blank if not found.
StartsWithStartsWith(Text, StartText) → BooleanEx: StartsWith(Title.Text, "Mgr")True if Text begins with StartText. Case-insensitive.
EndsWithEndsWith(Text, EndText) → BooleanEx: EndsWith(FileName, ".pdf")True if Text ends with EndText. Case-insensitive.
LeftINTLeft(Text, NumChars) → TextEx: Left("Power Fx", 5) → "Power"Returns leftmost N characters.
RightRight(Text, NumChars) → TextEx: Right("Power Fx", 2) → "Fx"Returns rightmost N characters.
MidINTMid(Text, StartNum, NumChars) → TextEx: Mid("Power Fx", 7, 2) → "Fx"Returns substring from a 1-based position.⭐ Combine Mid + Find to extract parts of structured strings like "Code:Value".
SplitINTSplit(Text, Separator) → Table{Result}Ex: Split("a,b,c", ",") → 3-row table with column "Result"Splits text into a single-column table (column = Result).⭐ Use with ForAll or Gallery Items to process comma-separated values stored in a text field.
UnicodeUnicode(Text) → NumberEx: Unicode("A") → 65Returns the Unicode code point of the first character.
CharChar(Number) → TextEx: Char(10) → newline · Char(9) → tab · Char(65) → "A"Returns the character for a given code point.⭐ Char(10) is the only way to embed a line break inside a Label or multi-line TextInput.
Transformation, Encoding & Format Conversion
LowerLower(Text) → TextEx: Lower("HELLO") → "hello"Converts all characters to lowercase.
UpperUpper(Text) → TextEx: Upper("hello") → "HELLO"Converts all characters to uppercase.
ProperProper(Text) → TextEx: Proper("power fx") → "Power Fx"Capitalises the first letter of each word.
TrimINTTrim(Text) → TextEx: Trim(" Hi there ") → "Hi there"Removes leading, trailing, and extra interior spaces.⭐ Trim vs TrimEnds: Trim collapses all excess spaces; TrimEnds only strips leading/trailing spaces.
TrimEndsTrimEnds(Text) → TextEx: TrimEnds(" Hi there ") → "Hi there"Strips only leading and trailing spaces; preserves interior spacing.
ReplaceReplace(Text, StartNum, NumChars, NewText) → TextEx: Replace("Power", 1, 5, "Canvas") → "Canvas"Replaces characters at a specific position and length. Position-based.
SubstituteINTSubstitute(Text, OldText, NewText [,InstanceNum]) → TextEx: Substitute("a-b-c", "-", "/") → "a/b/c"Replaces a substring by value. InstanceNum targets a specific occurrence.⭐ Replace uses position; Substitute uses value — classic interview distinction.
PlainTextPlainText(HTMLText) → TextEx: PlainText("<b>Hi</b>") → "Hi"Strips HTML tags and decodes entities. Use to sanitise RichTextEditor output.
EncodeHTMLEncodeHTML(Text) → TextEx: EncodeHTML("<b>") → "&lt;b&gt;"Encodes HTML special characters for safe rendering in HTML context.
EncodeUrlEncodeUrl(Text) → TextEx: EncodeUrl("hello world") → "hello%20world"URL-encodes a string. Required before embedding dynamic values in Launch() URLs.
TextINTText(Value [,FormatString]) → TextEx: Text(Today(), "dd-mmm-yyyy") → "01-Jun-2026" · Text(12500.5, "$#,##0.00")Converts number, date, or datetime to a formatted string.⭐ Key format tokens: dd/mm/yyyy · hh:mm AM/PM · $#,##0.00 · 0.00%. Text() is the go-to for all display formatting.
ValueINTValue(Text [,LanguageTag]) → NumberEx: Value("1,234.56") → 1234.56Parses text to a number. Respects locale decimal/thousand separators.⭐ Always wrap TextInput.Text in Value() before arithmetic to avoid type errors.
ConcatINTConcat(Table, Formula [,Separator]) → TextEx: Concat(Tags, Name, ", ") → "Tag1, Tag2"Evaluates formula on each table row and joins results into one string.⭐ Concat (table→text) vs Concatenate (text args). Use Concat to build comma-separated display lists.
ConcatenateDEPConcatenate(Text1, Text2, …) → TextEx: prefer First.Text & " " & Last.TextJoins text strings. Prefer the & operator — shorter and more readable.
Regex – Pattern Matching
IsMatchINTIsMatch(Text, Pattern [,Options]) → BooleanEx: IsMatch(EmailInput.Text, Match.Email)True if text matches a regular expression pattern. Options: Contains, Complete, BeginsWith, EndsWith, IgnoreCase, Multiline.⭐ Built-in patterns save writing raw regex: Match.Email · Match.Digits · Match.Letter · Match.MultipleLetters.
MatchMatch(Text, Pattern [,Options]) → RecordEx: Match("AB-123", "\d+").FullMatch → "123"Returns the first regex match as a record: FullMatch, SubMatches (table), StartMatch (position).
MatchAllMatchAll(Text, Pattern [,Options]) → TableEx: MatchAll("a1 b2", "[a-z]\d") → 2-row tableReturns a table of all regex matches. Each row: FullMatch, SubMatches, StartMatch.
Built-in Match patterns: Match.Email · Match.Digits · Match.Letter · Match.MultipleDigits · Match.MultipleLetters · Match.MultipleNonSpaces · Match.NonSpace · Match.OptionalDigits · Match.OptionalLetters · Match.MultilineNewline
Math & Statistics
Rounding, Arithmetic & Scientific
AbsAbs(Number) → NumberEx: Abs(-5) → 5Absolute value — removes the sign.
RoundINTRound(Number, Decimals) → NumberEx: Round(3.567, 2) → 3.57 · Round(2.5, 0) → 3Rounds half-away-from-zero.⭐ Round(2.5)=3, RoundDown(2.9)=2, RoundUp(2.1)=3 — common interview comparison.
RoundUpRoundUp(Number, Decimals) → NumberEx: RoundUp(2.1, 0) → 3Always rounds away from zero regardless of the decimal portion.
RoundDownRoundDown(Number, Decimals) → NumberEx: RoundDown(2.9, 0) → 2Always rounds toward zero (truncates toward zero).
CeilingCeiling(Number [,Significance]) → NumberEx: Ceiling(4.1) → 5 · Ceiling(7, 5) → 10Rounds up to the nearest multiple of Significance (default 1).
FloorFloor(Number [,Significance]) → NumberEx: Floor(4.9) → 4 · Floor(7, 5) → 5Rounds down to the nearest multiple of Significance (default 1).
IntInt(Number) → NumberEx: Int(3.9) → 3 · Int(-3.2) → -4Rounds toward negative infinity (true floor for all real numbers).
TruncTrunc(Number [,Decimals]) → NumberEx: Trunc(-3.9) → -3 · Trunc(3.9) → 3Truncates toward zero regardless of sign.⭐ Int(-3.2) = -4 but Trunc(-3.2) = -3 — subtle negative-number difference.
ModMod(Dividend, Divisor) → NumberEx: Mod(10, 3) → 1 · Mod(8, 2) → 0 (even check)Remainder after division. Use Mod(n,2)=0 to detect even numbers or wrap sequences.
PowerPower(Base, Exponent) → NumberEx: Power(2, 10) → 1024 (same as 2^10)Raises Base to Exponent. Equivalent to the ^ operator.
SqrtSqrt(Number) → NumberEx: Sqrt(144) → 12Square root.
ExpExp(Number) → NumberEx: Exp(1) → 2.71828 (e)Returns e raised to the given power (e^x).
LnLn(Number) → NumberEx: Ln(Exp(1)) → 1Natural logarithm (base e). Inverse of Exp.
LogLog(Number [,Base]) → NumberEx: Log(1000, 10) → 3 · Log(8, 2) → 3Logarithm. Default base is 10 when omitted.
PiPi() → NumberEx: Pi() → 3.14159265358979…Returns the mathematical constant π.
RandRand() → NumberEx: Rand() → 0.0 to 0.9999… (recalculates on every evaluation)Random decimal in [0,1). Recalculates on every formula evaluation — not cached.
RandBetweenRandBetween(Low, High) → NumberEx: RandBetween(1, 100) → random integer 1–100 inclusiveRandom integer between Low and High inclusive.
Aggregates & Statistics
SumINTSum(Table, Formula) or Sum(N1, N2, …) → NumberEx: Sum(Orders, Quantity * Price) · Sum(10, 20, 30) → 60Totals a formula across table rows, or sums scalar values.⭐ Table form is delegable on Dataverse when applied to a direct column. Collection form is not.
AverageAverage(Table, Formula) or Average(N1, N2, …) → NumberEx: Average(Scores, Value)Mean of values. Table form is delegable on Dataverse.
MaxINTMax(Table, Formula) or Max(N1, N2, …) → NumberEx: Max(Products, Price)Maximum value. Delegable on Dataverse.⭐ Max/Min on Dataverse tables delegate; on local collections they don't.
MinMin(Table, Formula) or Min(N1, N2, …) → NumberEx: Min(Tasks, DueDate)Minimum value. Delegable on Dataverse.
CountCount(Table, Formula) → NumberEx: Count(Orders, OrderNum)Counts rows where the formula evaluates to a numeric (non-blank) value.
CountACountA(Table, Formula) → NumberEx: CountA(Tasks, Title)Counts rows where the formula result is non-blank (any type).
CountIfINTDCountIf(Table, Condition) → NumberEx: CountIf(Tasks, Status = "Done")Counts rows matching a condition. Delegable on Dataverse.⭐ Use CountIf instead of CountRows(Filter(…)) — CountIf is delegable on Dataverse for large datasets.
CountRowsINTNDCountRows(Table) → NumberEx: CountRows(colCart) → number of items in collectionTotal number of rows in a table or collection. Never delegable.⭐ For accurate row counts on large Dataverse tables, use CountIf(Table, true) — it is delegable.
StdevPStdevP(Table, Formula) or StdevP(N1, N2, …) → NumberEx: StdevP(Results, Score)Population standard deviation of numeric values.
VarPVarP(Table, Formula) or VarP(N1, N2, …) → NumberEx: VarP(Results, Score)Population variance of numeric values.
Logical, Conditional & Error Handling
Conditionals, Boolean Logic & Blank / Type Checks
IfINTIf(Condition, ThenResult [,ElseResult]) → AnyEx: If(Score >= 60, "Pass", "Fail")Returns ThenResult when true, ElseResult when false. Can be chained: If(c1,r1, c2,r2, default).⭐ Switch is cleaner for 3+ branches. If re-evaluates each condition; Switch evaluates the formula once.
SwitchINTSwitch(Formula, Match1, Result1 [,…] [,Default]) → AnyEx: Switch(Status, "New","🔴", "Done","🟢", "⚫")Matches formula against a list of values and returns the corresponding result. More readable than nested If.⭐ Switch evaluates the formula once; nested If re-evaluates each branch condition — Switch is cheaper for 3+ cases.
And / &&And(Cond1, Cond2, …) → BooleanEx: And(Age > 18, HasID) or Age > 18 && HasIDTrue only when ALL conditions are true. Short-circuits on the first false.
Or / ||Or(Cond1, Cond2, …) → BooleanEx: Or(Role = "Admin", Role = "Owner")True when ANY condition is true. Short-circuits on the first true.
Not / !Not(Condition) → BooleanEx: Not(IsBlank(SearchBox.Text))Inverts a boolean value.
IsBlankINTIsBlank(Value) → BooleanEx: IsBlank(TextInput1.Text)True if value is blank or an empty string ("").⭐ IsBlank checks single values; IsEmpty checks tables — both appear in every canvas app validation pattern.
IsEmptyINTIsEmpty(Table) → BooleanEx: IsEmpty(colCart) · IsEmpty(Filter(Tasks, Status="Open"))True if the table or collection has zero rows.⭐ IsEmpty(Filter(…)) is more efficient than CountRows(Filter(…)) = 0 for delegation awareness.
BlankBlank() → BlankEx: Set(varUser, Blank())Returns an explicit blank value. Use to reset variables or clear optional lookup fields.
CoalesceINTCoalesce(Value1, Value2, …) → AnyEx: Coalesce(UserInput.Text, "Default value")Returns the first non-blank value in the list.⭐ Cleaner than If(IsBlank(x), fallback, x). Essential for providing safe defaults on optional fields.
IsNumericIsNumeric(Text) → BooleanEx: IsNumeric("123abc") → false · IsNumeric("42") → trueTrue if text can be parsed as a valid number. Use for input validation before calling Value().
IsTypeIsType(Record, TypeName) → BooleanEx: If(IsType(item.Owner, Contacts), AsType(item.Owner, Contacts).FullName)Checks whether a polymorphic record is of a specific Dataverse entity type. Always use before AsType.
BooleanBoolean(TextOrNumber) → BooleanEx: Boolean("yes") → true · Boolean(0) → falseParses "true"/"false"/"yes"/"no"/1/0 strings to Boolean. Useful for SharePoint Yes/No fields stored as text.
Error Handling
IfErrorINTIfError(Value, Replacement [,Value2, Replacement2, …]) → AnyEx: IfError(1 / Qty, 0) · IfError(Patch(…), Notify("Error: " & FirstError.Message))Returns Value; if it is an error, returns Replacement instead.⭐ Essential for safe division and Patch operations. Always wrap Patch in IfError in production apps.
IsErrorIsError(Value) → BooleanEx: If(IsError(Value(TextInput.Text)), "Not a number", "")True if the expression produced an error. Use to show contextual validation messages.
ErrorError({Kind: ErrorKind.xxx, Message: "…"}) → ErrorEx: Error({Kind: ErrorKind.Custom, Message: "Age is required"})Raises a custom error that propagates up and is caught by an enclosing IfError.
ErrorsINTErrors(DataSource [,Record]) → TableEx: Errors(Accounts, EditForm1.LastSubmit)Returns error details from the last data-source operation. Columns: Record, Column, Message, Error (ErrorKind).⭐ Use in OnFailure of SubmitForm to surface field-level validation errors from Dataverse business rules.
ErrorKind values: Conflict · ConstraintViolation · CreatePermission · DeletePermission · EditPermission · GeneratedValue · None · NotFound · ReadPermission · Sync · Unknown · Validation · Custom
Date & Time
Construction & Parsing
NowINTNow() → DateTimeEx: Now() → current date + time (live)Current date and time. Recalculates continuously.⭐ Now() returns DateTime; Today() returns Date (midnight). Both recalculate live — common interview pair.
TodayINTToday() → DateEx: Today() → current date at midnightReturns today's date with no time component. Use for date comparisons and arithmetic.
DateDate(Year, Month, Day) → DateEx: Date(2026, 12, 31) · Date(2026, 13, 1) → 1 Jan 2027 (overflow wraps)Constructs a Date from year/month/day integers. Month and Day overflow wraps correctly.
TimeTime(Hour, Minute, Second) → TimeEx: Time(9, 30, 0) → 09:30:00 · Add to a Date with + to get DateTimeConstructs a Time value. Add to a Date using the + operator to build a full DateTime.
DateTimeValueDateTimeValue(Text [,LanguageTag]) → DateTimeEx: DateTimeValue("2026-06-01T09:00:00")Parses a text string into a DateTime. Supports ISO 8601 and locale-specific formats.
DateValueDateValue(Text [,LanguageTag]) → DateEx: DateValue("01/06/2026", "en-GB")Parses text to a Date. LanguageTag resolves ambiguous formats (mm/dd vs dd/mm).
TimeValueTimeValue(Text [,LanguageTag]) → TimeEx: TimeValue("9:30 AM")Parses a text string to a Time value.
Arithmetic & Component Extraction
DateAddINTDateAdd(DateTime, Delta [,Units]) → DateTimeEx: DateAdd(Today(), 30, TimeUnit.Days) · DateAdd(Today(), -1, TimeUnit.Months)Adds or subtracts a duration. Units: TimeUnit.Days · Months · Years · Hours · Minutes · Seconds · Weeks · Quarters.⭐ Always specify TimeUnit explicitly — improves readability and is expected in code reviews.
DateDiffINTDateDiff(Start, End [,Units]) → NumberEx: DateDiff(BirthDate, Today(), TimeUnit.Years) → age in yearsReturns the difference between two dates in the given units. Negative if Start > End.⭐ DateDiff(BirthDate, Today(), TimeUnit.Years) is the standard age-calculation pattern.
TimeZoneOffsetTimeZoneOffset([DateTime]) → Number (minutes)Ex: TimeZoneOffset() → -330 for IST (UTC+5:30)Returns the local UTC offset in minutes. Use to normalise UTC-stored timestamps to local time.
DayDay(DateTime) → NumberEx: Day(Today()) → 1–31Day of the month component.
MonthMonth(DateTime) → NumberEx: Month(Today()) → 1–12 (January = 1)Month number from a date.
YearYear(DateTime) → NumberEx: Year(Today()) → 20264-digit year from a date.
HourHour(DateTime) → NumberEx: Hour(Now()) → 0–23Hour component (24-hour clock).
MinuteMinute(DateTime) → NumberEx: Minute(Now()) → 0–59Minute component from a datetime.
SecondSecond(DateTime) → NumberEx: Second(Now()) → 0–59Second component from a datetime.
WeekdayWeekday(DateTime [,StartOfWeek]) → NumberEx: Weekday(Today(), StartOfWeek.Monday) → 1=Mon … 7=SunDay of week as a number. StartOfWeek.Monday makes Monday = 1.
WeekNumWeekNum(DateTime [,StartOfWeek]) → NumberEx: WeekNum(Today()) → 1–54Week number of the year based on the locale start-of-week.
ISOWeekNumISOWeekNum(DateTime) → NumberEx: ISOWeekNum(Today())ISO 8601 week number. Monday = first day; Week 1 = week containing the year's first Thursday.
Calendar & Clock Enumerations
Calendar.MonthsLongCalendar.MonthsLong() → Table{Value}Ex: Gallery Items: Calendar.MonthsLong() → ["January", "February", …]Locale-aware full month names as a single-column table. Bind directly to a Dropdown or Gallery.
Calendar.MonthsShortCalendar.MonthsShort() → Table{Value}Ex: Calendar.MonthsShort() → ["Jan", "Feb", …]Abbreviated month names for the current locale.
Calendar.WeekdaysLongCalendar.WeekdaysLong() → Table{Value}Ex: Calendar.WeekdaysLong() → ["Sunday", "Monday", …]Full weekday names for the current locale.
Calendar.WeekdaysShortCalendar.WeekdaysShort() → Table{Value}Ex: Calendar.WeekdaysShort() → ["Sun", "Mon", …]Abbreviated weekday names for the current locale.
Clock.AmPmClock.AmPm → TextEx: Clock.AmPm → "AM" or "PM" (locale-aware)Locale-appropriate AM/PM designation string.
Clock.AmPmShortClock.AmPmShort → TextEx: Clock.AmPmShort → "a" or "p" (abbreviated)Abbreviated AM/PM string for the current locale.
Clock.IsClock24Clock.IsClock24 → BooleanEx: If(Clock.IsClock24, "HH:mm", "h:mm AM/PM")True if the device uses a 24-hour clock. Use for conditional time format strings in Text().
Table Operations
Filter, Search & Lookup
FilterDINTFilter(Table, Cond1 [,Cond2, …]) → TableEx: Filter(Accounts, Status = "Active", Region = ddRegion.Selected.Value)Returns rows matching all conditions (AND logic for multiple conditions).⭐ Most common interview topic. Delegable on Dataverse for =, <, >, in, And, Or. Complex/computed expressions may not delegate.
SearchNDINTSearch(Table, SearchText, Col1 [,Col2, …]) → TableEx: Search(Products, SearchBox.Text, "Name", "SKU")Case-insensitive substring search across named text columns. Always client-side.⭐ Search is NEVER delegable. Use Filter + StartsWith for delegable text search on large Dataverse tables.
LookUpDINTLookUp(Table, Condition [,Result]) → Record | ValueEx: LookUp(Employees, Email = User().Email, FullName)Returns the first matching record (or a field from it). Delegable on Dataverse.⭐ LookUp returns ONE record; Filter returns a TABLE. This distinction is asked in every interview.
Sort, Slicing & Row Access
SortINTSort(Table, Formula [,Order]) → TableEx: Sort(Products, Price, SortOrder.Descending)Sorts by a formula. SortOrder.Ascending (default) or SortOrder.Descending.⭐ Sort on a computed formula = NOT delegable. SortByColumns on a real column = delegable.
SortByColumnsDSortByColumns(Table, Col, Order [,Col2, Order2, …]) → TableEx: SortByColumns(Accounts, "Name", SortOrder.Ascending)Sorts by one or more real column names (as text strings). Delegable on Dataverse and SharePoint.
ShuffleShuffle(Table) → TableEx: Shuffle(Questions)Returns the table in a random order. Always client-side.
FirstINTFirst(Table) → RecordEx: First(SortByColumns(Orders, "Created", SortOrder.Descending))Returns the first record. Returns blank if the table is empty.⭐ First(SortByColumns(…)) is the standard pattern to get the newest or highest-value record.
LastLast(Table) → RecordEx: Last(colHistory)Returns the last record of a table or collection.
FirstNFirstN(Table, N) → TableEx: FirstN(TopProducts, 5)Returns the first N rows as a table.
LastNLastN(Table, N) → TableEx: LastN(AuditLog, 10)Returns the last N rows as a table.
IndexIndex(Table, RowIndex) → RecordEx: Index(Gallery1.AllItems, 3)Returns the record at a 1-based row index. Useful for custom pagination controls.
SampleSample(Table, N) → TableEx: Sample(Questions, 10)Returns N randomly selected rows. Client-side only. Useful for quiz/survey scenarios.
ForAll, Sequence, Grouping & Table Construction
ForAllINTNDForAll(Table, Formula) → TableEx: ForAll(colItems, Patch(Orders, Defaults(Orders), {Name: Name, Qty: Qty}))Evaluates a formula for every row; returns the resulting table. Always client-side.⭐ ForAll is non-delegable. For delegable batch writes use Patch(DataSource, Table) instead.
SequenceINTSequence(Count [,Start] [,Step]) → Table{Value}Ex: Sequence(5) → [1,2,3,4,5] · Sequence(5, 0, 10) → [0,10,20,30,40]Generates a single-column table of sequential numbers.⭐ Sequence is the cleanest way to build custom paging controls or repeat operations N times.
TableTable(Record1, Record2, …) → TableEx: [{Name:"A", Val:1}, {Name:"B", Val:2}] (shorthand)Constructs an inline table from record literals. Prefer the [] shorthand syntax for readability.
DistinctINTDistinct(Table, Formula) → Table{Result}Ex: Distinct(Products, Category)Returns unique values of the formula as a single-column table (column = Result).⭐ Classic dropdown pattern: Dropdown Items = Distinct(DataSource, ColumnName). Partially delegable on Dataverse.
GroupByGroupBy(Table, Col1 [,Col2, …], GroupColName) → TableEx: GroupBy(Orders, "Region", "RegionOrders")Groups rows by column values. Nested rows stored in the GroupColName column as a sub-table.
UngroupUngroup(Table, ColName) → TableEx: Ungroup(grouped, "RegionOrders")Flattens a grouped table back to a flat table. Inverse of GroupBy.
ZipZip(Table1, Table2 [,…]) → TableEx: Zip(Names, Scores) → combined by row indexCombines multiple tables by row index into one table of records. Stops at the shortest table.
Column Shaping
AddColumns · DropColumns · RenameColumns · ShowColumns · AsType · IsType
AddColumnsINTAddColumns(Table, ColName, Formula [,ColName2, Formula2, …]) → TableEx: AddColumns(Products, "TotalValue", Price * Stock, "Label", Name & " (" & SKU & ")")Returns a new table with extra computed columns added. Does NOT modify the source.⭐ AddColumns is the Power Fx equivalent of SQL SELECT with computed columns — heavily used in Gallery Items.
DropColumnsDropColumns(Table, ColName [,ColName2, …]) → TableEx: DropColumns(Accounts, "InternalID", "AuditField")Returns a table with specified columns removed. Useful to reduce payload before Patch.
RenameColumnsRenameColumns(Table, OldName, NewName [,…]) → TableEx: RenameColumns(Result, "cr123_name", "ProductName", "cr123_price", "Price")Renames columns. Essential for mapping Dataverse internal/publisher-prefixed names to readable display names.
ShowColumnsShowColumns(Table, ColName [,ColName2, …]) → TableEx: ShowColumns(Contacts, "FullName", "Email", "Phone")Returns a table with ONLY the specified columns (whitelist). Inverse of DropColumns.
AsTypeAsType(Record, TypeName) → RecordEx: AsType(item.Owner, Contacts).FullNameCasts a polymorphic Dataverse lookup to a specific entity type to access its typed columns.
IsTypeIsType(Record, TypeName) → BooleanEx: If(IsType(item.Owner, Contacts), AsType(item.Owner, Contacts).FullName, "—")Checks the runtime type of a polymorphic record. Always use IsType before AsType to avoid errors.
These functions return a new table — they never modify the source. Chain them: AddColumns(RenameColumns(Filter(…))). All are non-delegable (client-side processing).
Collections & Patch (Data Mutation)
Collection Operations – Collect · ClearCollect · Clear · Remove · RemoveIf · Update · UpdateIf
CollectINTCollect(Target, Item1 [,Item2, …]) → TableEx: Collect(colCart, {Name: Product.Name, Qty: 1, Price: Product.Price})Appends records to a collection. Does NOT clear existing data first.⭐ Collect ADDS; ClearCollect REPLACES — the most common interview mix-up on collection functions.
ClearCollectINTClearCollect(Target, Table) → TableEx: ClearCollect(colProducts, Filter(Products, Active = true))Clears the collection first then loads new data. Standard pattern for caching on app start.⭐ Put ClearCollect in App OnStart (inside Concurrent) to pre-load data and reduce screen-load latency.
ClearINTClear(Collection) → VoidEx: Clear(colCart)Empties all rows from the collection. The collection variable still exists.⭐ Use Clear to reset a shopping cart or a multi-step selection buffer between sessions.
RemoveRemove(Source, Record1 [,Record2, …] [,All]) → VoidEx: Remove(colCart, ThisItem)Removes specific records from a collection or data source by record reference. All removes all matching.
RemoveIfRemoveIf(Source, Condition [,Condition2, …]) → VoidEx: RemoveIf(colCart, Qty = 0)Removes all rows matching the condition. Works on both collections and data sources.
UpdateUpdate(Collection, OldRecord, NewRecord [,All]) → VoidEx: prefer UpdateIf or Patch insteadReplaces an entire record in a collection. Rarely used in modern apps — prefer UpdateIf or Patch.
UpdateIfUpdateIf(Collection, Condition, ChangeRecord [,…]) → VoidEx: UpdateIf(colCart, ID = selectedID, {Qty: NewQty})Updates matching rows in a local collection. Does not work on server data sources — use Patch for those.
Patch, Defaults, Refresh & Errors – Server Data Source Write Operations
PatchINTPatch(DataSource, BaseRecord, ChangeRecord [,…]) → RecordEx (create): Patch(Accounts, Defaults(Accounts), {Name: "Acme", Status: "Active"})
Ex (update): Patch(Accounts, LookUp(Accounts, ID = varID), {Status: "Closed"})
Creates or updates a record by merging ChangeRecord into BaseRecord.⭐ Top interview topic. Defaults(DataSource) = create new; existing record = update. Always wrap in IfError.
Patch (bulk)Patch(DataSource, Table) → TableEx: Patch(Orders, colPendingOrders)Upserts all rows of a table into the data source in a single operation. Far more efficient than ForAll + Patch per row.
DefaultsINTDefaults(DataSource) → RecordEx: Patch(Tasks, Defaults(Tasks), {Title: txtTitle.Text, AssignedTo: User().Email})Returns a blank record with default values for the data source. Use as BaseRecord to tell Patch to create a new record.⭐ Defaults is the only way to signal "create new" to Patch — it provides the empty base row.
RefreshRefresh(DataSource) → VoidEx: Refresh(Accounts)Forces the data source to reload from the server, clearing the local cache.
ErrorsINTErrors(DataSource [,Record]) → TableEx: Errors(Accounts, EditForm1.LastSubmit)Returns error details from the last data operation. Columns: Record, Column, Message, Error (ErrorKind).⭐ Call in OnFailure of SubmitForm to surface column-level validation errors from Dataverse business rules.
Form Functions
SubmitForm · ResetForm · NewForm · EditForm · ViewForm
SubmitFormINTSubmitForm(FormControl) → VoidEx: SubmitForm(EditForm1)Validates all data cards then calls Patch on the data source. OnSuccess / OnFailure fire after.⭐ SubmitForm is declarative — use OnSuccess to navigate away and Errors() in OnFailure to display messages.
ResetFormINTResetForm(FormControl) → VoidEx: ResetForm(EditForm1)Resets all data card fields to their original values and clears unsaved changes.⭐ ResetForm does NOT clear the Item property. Combine with Set(varItem, Blank()) to fully reset a form.
NewFormNewForm(FormControl) → VoidEx: NewForm(EditForm1)Sets the form to New mode — all fields blank, ready to create a new record.
EditFormEditForm(FormControl) → VoidEx: EditForm(EditForm1)Switches the form from View mode to Edit mode, making all data card fields editable.
ViewFormViewForm(FormControl) → VoidEx: ViewForm(EditForm1)Sets the form to View (read-only) mode. Fields display their values but cannot be edited.
Form.Mode: FormMode.Edit (0) · FormMode.New (1) · FormMode.View (2)  |  Form.LastSubmit: record returned by last successful SubmitForm  |  Form.Valid: true when all required cards pass validation
Navigate · Back · Launch · Exit · Download · Notify
NavigateINTNavigate(Screen [,Transition] [,ContextRecord]) → VoidEx: Navigate(DetailScreen, ScreenTransition.Fade, {locRecord: ThisItem, locMode: "Edit"})Navigates to a screen. ContextRecord sets UpdateContext variables on the target screen.⭐ ContextRecord in Navigate is the primary way to pass data between screens — sets local variables on the target.
BackINTBack([Transition]) → VoidEx: Back(ScreenTransition.UnCover)Returns to the previous screen in the navigation stack.⭐ Back() pops the navigation stack; Navigate() pushes. Use Back() for cancel and dismiss patterns.
LaunchLaunch(URL [,ParamName, ParamValue, …]) → VoidEx: Launch("https://teams.microsoft.com/…", "userId", EncodeUrl(User().Email))Opens a URL in the browser or an external app. Use EncodeUrl() for dynamic query parameter values.
ExitExit([Value]) → VoidEx: Exit()Closes the app. Optional Value is passed back to the embedding context (model-driven / Teams).
DownloadDownload(URL) → VoidEx: Download(Attachment.AbsoluteUri)Initiates a file download to the device. Works in browser-based canvas apps.
NotifyINTNotify(Message [,NotificationType] [,Timeout]) → VoidEx: Notify("Saved!", NotificationType.Success, 3000)Shows a banner notification. Default timeout: 10 s.⭐ Always call Notify in OnSuccess / OnFailure of SubmitForm to give the user visible feedback.
NotificationType: NotificationType.Success · Error · Warning · Information  |  ScreenTransition: None · Fade · Cover · CoverRight · UnCover · UnCoverRight · Push · Pop
Variables & Scope
Set · UpdateContext · With
SetINTSet(VariableName, Value) → VoidEx: Set(gblUserRole, LookUp(Employees, Email = User().Email, Role))Sets a global variable accessible from ALL screens. Creates the variable on first use — no declaration needed.⭐ Set = global (all screens); UpdateContext = local (current screen only). Top variable interview question.
UpdateContextINTUpdateContext({VarName: Value, Var2: Value2, …}) → VoidEx: UpdateContext({locIsLoading: true, locSelectedID: ThisItem.ID})Creates or updates screen-local variables (scoped to the current screen, destroyed on navigation).⭐ Naming convention: "loc" prefix for local (UpdateContext), "gbl" prefix for global (Set).
WithINTWith({Alias: Value, …}, Formula) → AnyEx: With({emp: LookUp(Employees, ID = Gallery1.Selected.ID)}, emp.Name & " – " & emp.Dept)Creates immutable named aliases scoped to a formula. Avoids repeating expensive lookups multiple times.⭐ With is Power Fx's "let" construct. Use it to store intermediate LookUp results — single evaluation, no side effects.
Control Behavior
Reset · Select · SetFocus · PDF · RequestHide
ResetINTReset(Control) → VoidEx: Reset(TextInput1); Reset(Dropdown1)Resets a control to its Default property value. Works on inputs, dropdowns, sliders, date pickers.⭐ Reset(control) resets one control; ResetForm(form) resets all data cards in a form at once.
SelectSelect(Control [,Row]) → VoidEx: Select(BtnSave) · Select(Gallery1, 1)Programmatically fires the OnSelect of a control or a specific gallery row. Useful for automation and testing.
SetFocusSetFocus(Control) → VoidEx: SetFocus(SearchBox)Moves keyboard focus to the specified control. Use after navigation or form submission.
PDFPDF(Control1 [,Control2, …]) → PDF objectEx: PDF(Screen1) · pass result to Power Automate as attachmentGenerates a PDF from one or more controls or a full screen. Pass the result to a flow for email or storage.
RequestHideRequestHide() → VoidEx: RequestHide() (inside a Teams or model-driven panel)Requests that the hosting Power Apps panel be hidden. Used in embedded Teams / model-driven panel scenarios.
User & Environment
User() · Param() · Language() · Connection · Host
User()INTUser() → Record {Email, FullName, Image}Ex: User().Email → "vipin@org.com" · User().FullName → "Vipin Tyagi"Returns the current signed-in user's Email, FullName (display name), and Image (photo URL).⭐ Filter data by current user: Filter(Tasks, AssignedTo = User().Email). No extra connector required.
ParamINTParam(ParameterName) → TextEx: Param("recordId") → value passed via Launch() or deep link URLReads a URL or launch parameter passed when the app was opened.⭐ Deep-link pattern: Launch(appURL, "recordId", selectedID) → Param("recordId") on App OnStart.
LanguageLanguage() → TextEx: Language() → "en-US" · "fr-FR" · "de-DE"Returns the current user's locale tag. Use to conditionally format dates or numbers per locale.
Connection.ConnectedConnection.Connected → BooleanEx: If(!Connection.Connected, Notify("You are offline", NotificationType.Warning))True when the device has a network connection. Use for offline-aware UX.
Host.nameHost.name → TextEx: Host.name → "Power Apps" · "Microsoft Teams" · "SharePoint"Identifies the hosting environment. Adapt UI layout or features based on the host.
Host.sessionIDHost.sessionID → TextEx: Host.sessionID → unique GUID per sessionUnique session identifier. Useful for telemetry, diagnostics logging, and support cases.
Color Functions
RGBA · ColorValue · ColorFade · Color.*
RGBAINTRGBA(Red, Green, Blue, Alpha) → ColorEx: RGBA(0, 120, 212, 1) → Microsoft blue · RGBA(255, 0, 0, 0.5) → semi-transparent redConstructs a color. R/G/B: 0–255. Alpha: 0 (fully transparent) to 1 (fully opaque).⭐ Use RGBA for data-driven colors: RGBA(255, If(Status = "Late", 0, 255), 0, 1) — red for late, green otherwise.
ColorValueColorValue(ColorNameOrHex) → ColorEx: ColorValue("#0078D4") · ColorValue("OrangeRed")Converts a CSS named color or hex string to a Power Apps Color value.
ColorFadeColorFade(Color, FadeDelta) → ColorEx: ColorFade(RGBA(0,120,212,1), 0.5) → lighter blue · ColorFade(…, -0.3) → darkerLightens (positive delta) or darkens (negative delta) a color. FadeDelta: -1 to 1.
Color.*Color.Red, Color.Blue, Color.Transparent, Color.White, …Ex: Fill: Color.Transparent · Color: Color.DarkSlateGrayBuilt-in named color constants. Full CSS color set available as Color.ColorName enumeration.
Type Conversion
Value · Text · Boolean · Float · Decimal
ValueINTValue(Text [,LanguageTag]) → NumberEx: Value("3.14") → 3.14 · Value("1.234,56", "de-DE") → 1234.56Parses text to a floating-point number. LanguageTag handles locale separators.⭐ Required when doing math on any TextInput — text controls return Text type by default.
TextINTText(Value [,FormatString [,LanguageTag]]) → TextEx: Text(12500.5, "$#,##0.00") → "$12,500.50" · Text(Today(), "dd-mmm-yyyy")Converts number, date, datetime, or boolean to a formatted string. Essential for all display formatting.
BooleanBoolean(TextOrNumber) → BooleanEx: Boolean("yes") → true · Boolean(0) → false · Boolean("TRUE") → trueParses "true"/"false"/"yes"/"no"/1/0 to Boolean. Useful for SharePoint Yes/No fields stored as text.
FloatFloat(TextOrNumber) → FloatEx: Float("3.14") → 3.14 (IEEE 754 double)Explicit conversion to IEEE 754 double-precision float. Use when interfacing with external numeric types.⭐ Canvas apps still use Float for every number today — Number, plain numeric literals, and Value() all resolve to Float. Decimal support for canvas apps is on Microsoft's roadmap but not shipped.
DecimalDecimal(TextOrNumber) → DecimalEx: Decimal("19.99")Converts to an exact base-10 decimal — avoids Float's base-2 rounding.⚠️ On other Power Fx hosts (Dataverse formula columns, pac power-fx repl) Decimal is already the default for plain literals like 1.234. Canvas apps are the exception — per Microsoft's own docs, "Power Apps only supports Float today… Decimal support will be added soon." Use Decimal() for interop with a Dataverse Decimal column; don't assume canvas math is exact.
JSON & Local Storage
JSON · SaveData · LoadData · ClearData
JSONINTJSON(Value [,Format]) → TextEx: JSON(colCart, JSONFormat.IndentFour) · JSON({Name:"A", Qty:2})Serialises a value, record, or table to a JSON string. Format: Compact (default) · IndentFour · IgnoreUnsupportedTypes · IncludeBinaryData.⭐ Use JSON() to send data to Power Automate HTTP connectors as a request body string.
SaveDataSaveData(Collection, Name) → VoidEx: SaveData(colDraft, "formDraft")Persists a collection to the device's local storage. Survives app restarts. Native mobile apps only.
LoadDataLoadData(Collection, Name [,IgnoreNonexistentFile]) → VoidEx: LoadData(colDraft, "formDraft", true)Restores a previously saved collection from local storage. Set IgnoreNonexistentFile = true to skip errors if the file is missing.⭐ SaveData/LoadData power offline-capable apps: store drafts locally, sync when Connection.Connected is true.
ClearDataClearData([Name]) → VoidEx: ClearData("formDraft") · ClearData() → removes all saved dataDeletes saved data from local storage by name. Omit Name to clear all saved data.
Bitwise Functions
BitAnd · BitOr · BitXor · BitNot · BitShiftLeft · BitShiftRight
BitAndBitAnd(Num1, Num2) → NumberEx: BitAnd(12, 10) → 8  (1100 & 1010 = 1000)Bitwise AND. Use for permission flag mask checks — BitAnd(userFlags, requiredFlag) ≠ 0 means permission is set.
BitOrBitOr(Num1, Num2) → NumberEx: BitOr(12, 10) → 14  (1100 | 1010 = 1110)Bitwise OR. Use to combine multiple permission flags into a single integer.
BitXorBitXor(Num1, Num2) → NumberEx: BitXor(12, 10) → 6  (XOR)Bitwise XOR. Toggle individual flags or detect bit-level differences.
BitNotBitNot(Number) → NumberEx: BitNot(0) → -1  (all bits set)Bitwise NOT — flips all bits (one's complement).
BitShiftLeftBitShiftLeft(Number, Bits) → NumberEx: BitShiftLeft(1, 3) → 8  (multiply by 2³)Shifts bits left by N positions. Equivalent to multiplying by 2^N.
BitShiftRightBitShiftRight(Number, Bits) → NumberEx: BitShiftRight(8, 3) → 1  (divide by 2³)Shifts bits right by N positions. Equivalent to integer-dividing by 2^N.
AI Builder Functions
AIClassify · AIExtract · AIReply · AISentiment · AISummarize · AISummarizeRecord · AITranslate
AIClassifyINTEnvironment.AIClassify({Text:…, Categories:…}) → Record{Classification}Ex: Set(Result, Environment.AIClassify({Text: Feedback.Text, Categories: ["Positive","Negative","Neutral"]}).Classification)Classifies text into one of the provided categories.⭐ In canvas apps these are called on the Environment namespace with ONE record argument (named columns) — not positional args like other Power Fx hosts. Add the Environment data source first.
AIExtractEnvironment.AIExtract({Text:…, Entity:…}) → Record{ExtractedData: Table}Ex: Set(Result, Environment.AIExtract({Text: Invoice.Text, Entity: "InvoiceAmount"}).ExtractedData)Extracts a single named entity from unstructured text. One entity per call — not a list of field names. Returns a table via .ExtractedData, zero or more matches.
AIReplyINTEnvironment.AIReply({Text:…}) → Record{PreparedResponse}Ex: Set(Result, Environment.AIReply({Text: UserMessage.Text}).PreparedResponse)Generates a conversational reply.⭐ AIReply powers in-app copilot experiences without leaving Power Apps — great interview talking point for AI-augmented apps.
AISentimentEnvironment.AISentiment({Text:…}) → Record{AnalyzedSentiment}Ex: Set(Result, Environment.AISentiment({Text: ReviewText.Text}).AnalyzedSentiment) → "Positive"Analyses sentiment. Returns "Positive" / "Negative" / "Neutral".
AISummarizeEnvironment.AISummarize({Text:…}) → Record{SummarizedText}Ex: Set(Result, Environment.AISummarize({Text: LongDoc.Text}).SummarizedText)Summarises long text into a shorter passage.
AISummarizeRecordNewEnvironment.AISummarizeRecord(Record) → Record{SummarizedText}Ex: Set(Result, Environment.AISummarizeRecord(First(Accounts)).SummarizedText)Summarises a whole Dataverse record directly — no need to first flatten its fields into text.
AITranslateEnvironment.AITranslate({Text:…, TargetLanguage:…}) → Record{TranslatedText}Ex: Set(Result, Environment.AITranslate({Text: Message.Text, TargetLanguage: "fr"}).TranslatedText)Translates text to the target language (BCP-47 tag: "fr", "de", "ja", "hi", …). Source language is auto-detected.
⚠️ These are behavior functions — they can't be used directly in a control's data property (e.g. a Text control's Text). Call them from OnSelect/OnVisible etc. via Set(varName, Environment.AIxxx({...}).Column), then bind the property to varName. Requires Power Apps Premium (or AI Builder capacity) and consumes AI Builder credits first, then Copilot Credits. AI Builder credits are being phased out: no new add-ons, and no seeded credits on licenses bought or renewed from 1 Nov 2026. Runs server-side.
User-Defined Types, Functions & Named Formulas GA
App.Formulas — Named Formulas
Named formulaName = Expression;Ex: BGColor = ColorValue(Param("BackgroundColor"));Defines an immutable, always-available, auto-recalculating value inside the App.Formulas property. Replaces the OnStart-variable pattern — no timing dependency.⭐ The Excel-named-cell equivalent. Calculation is deferred until first use — a formula only needed on screen2 isn't computed until screen2 is shown.
Chained formulasA = expr1; B = A + 1;Ex: UserInfo = LookUp(Users, 'Primary Email' = User().Email); UserTitle = UserInfo.Title;Named formulas can reference each other in any order (no circular references) and are evaluated in parallel — order in App.Formulas doesn't matter.
Limitations: can't call behavior functions (Set, Notify, Patch, Collect…) or otherwise cause side effects, and can't form a circular reference (a = b; b = a; is invalid).
User-Defined Functions (UDF)
Pure UDFINTFnName(Param: Type, …): ReturnType = Formula;Ex: LibraryGenre(SelectedGenre: Text): LibraryType = Filter(Library, Genre = SelectedGenre);A named formula with parameters — call it like a built-in function, e.g. LibraryGenre("Fiction").⭐ Every parameter and the return value must be typed: a built-in type name, a data source name, or a type built with Type().
Behavior UDFFnName(Param: Type): Void = { Formula1; Formula2 };Ex: Spend(Amount: Number): Void = { Set(Savings, Savings - Amount); Set(Spent, Spent + Amount) };Wrap the body in { } to allow Set/Notify/Patch-style side effects. Return type is Void when nothing is returned.
⚠️ Recursion isn't yet supported. A record-type parameter's fields must be a proper subset of the type definition — extra fields error out (e.g. passing {Title:"x", Published:2001} where the type only declares Title fails).
User-Defined Types (UDT) — Type() & RecordOf()
TypeINTTypeName := Type(TypeSpecification)Ex: BookType := Type({ Title: Text, Author: Text, Published: Number });Declares a named, reusable type for UDF signatures, ParseJSON, and untyped-object conversions. Use :=, not =.⭐ A record type spec mirrors record-literal syntax with type names instead of values: {Name:"Jane"} → Type({Name: Text}).
Table typeTableType := Type( [ RecordType ] )Ex: LibraryType := Type( [ BookType ] );Wrap a record type in square brackets for the type of a table of those records — square brackets here, not the Table() function.
RecordOfRecordOf(TableType) → TypeEx: BookType := Type( RecordOf( LibraryType ) );Extracts the single-record type from an existing table type. Valid only inside Type() — use when you have the table type first and need the row type.
ParseJSON + UDTParseJSON(JsonText, UDT) → RecordEx: ParseJSON(jsonText, BookType) → typed record, no per-field IsType/AsType neededPassing a user-defined type as ParseJSON's second argument returns a fully typed record directly.
✅ Generally available (graduated from experimental preview) — usable in canvas apps' App.Formulas, custom connectors, and component libraries. Declare named types like Customer, OrderLine, InvoiceHeader once and reuse them everywhere instead of repeating inline type specs.
Delegation Guide
Which Functions Delegate? – Dataverse vs SharePoint
FunctionDataverseSharePoint    Notes
Filter✔ Delegable✔ Delegable  · Simple predicates (=, <, >, And, Or, in). Complex/computed expressions fall back to client.
LookUp✔ Delegable✔ Delegable  · Single-record fetch — fully delegable with simple conditions.
SortByColumns✔ Delegable✔ Delegable  · Sort on real column names. Computed expressions are non-delegable.
Sort (formula)✘ Not Delegable✘ Not Delegable  · Sort on a computed formula is always client-side.
Search✘ Not Delegable✘ Not Delegable  · Always client-side. Use Filter + StartsWith for delegable text search on Dataverse.
StartsWith (in Filter)✔ Delegable✘ Not Delegable  · Dataverse: delegable text prefix. SharePoint: client-side only.
CountIf✔ Delegable✘ Not Delegable  · Dataverse: delegable. SharePoint: capped at 500 rows.
Sum / Average / Max / Min✔ Delegable✘ Not Delegable  · Table aggregate form delegates on Dataverse only.
CountRows✘ Not Delegable✘ Not Delegable  · Never delegable. Use CountIf(Table, true) on Dataverse for accurate large counts.
Distinct~ Partial✘ Not Delegable  · Dataverse: delegable on simple columns only. Computed formula = non-delegable.
In (membership)✔ Delegable~ Partial  · Filter(Table, ID in colIDs) — delegable on Dataverse for choice/lookup columns.
IsBlank (in Filter)✔ Delegable~ Partial  · Dataverse: delegable. SharePoint: may be non-delegable depending on column type.
ForAll✘ Not Delegable✘ Not Delegable  · Always client-side. Use Patch(DataSource, Table) for delegable bulk writes instead.
⚠️ Delegation limit default: 500 rows (raise to 2 000 in Settings → Data row limit). Data beyond the limit is silently dropped. Use Filter + SortByColumns as the safest delegable combination for large tables.
Common Pattern Recipes
Data & Collection Patterns
1. Create a new Dataverse record (Patch + Defaults)
IfError( Patch(Accounts, Defaults(Accounts), {Name: txtName.Text, Status: "Active"}), Notify("Error: " & FirstError.Message, NotificationType.Error) ); Notify("Account created!", NotificationType.Success);
Wrap every Patch in IfError — Dataverse business rule violations surface as errors, not exceptions.
2. Delegable text search with StartsWith
// Gallery Items (delegates on Dataverse): Filter(Contacts, StartsWith(FullName, SearchBox.Text) || StartsWith(Email, SearchBox.Text))
StartsWith delegates on Dataverse. Search() does NOT — avoid Search() on tables with 500+ rows.
3. Cache data on app start with Concurrent
// App OnStart: Concurrent( ClearCollect(colProducts, Filter(Products, Active = true)), ClearCollect(colCategories, Distinct(Products, Category)), Set(gblCurrentUser, LookUp(Employees, Email = User().Email)) );
Concurrent() runs multiple ClearCollect calls in parallel — significantly faster than sequential.
4. Cascading dropdown
// ddCategory Items: Distinct(Products, Category) // ddProduct Items: Filter(Products, Category = ddCategory.Selected.Result)
Filter the child dropdown on the parent's Selected value. Delegable on Dataverse.
5. Shopping cart upsert (add or increment)
If(CountIf(colCart, ID = ThisItem.ID) = 0, Collect(colCart, {ID: ThisItem.ID, Name: ThisItem.Name, Qty: 1, Price: ThisItem.Price}), UpdateIf(colCart, ID = ThisItem.ID, {Qty: Qty + 1}) ); // Cart total: Sum(colCart, Qty * Price)
CountIf checks existence; Collect adds; UpdateIf increments — clean upsert on a collection.
6. Batch write a collection to Dataverse
IfError( Patch(Orders, colPendingOrders), Notify("Batch failed: " & FirstError.Message, NotificationType.Error) ); Clear(colPendingOrders);
Single Patch(DataSource, Table) is far more efficient than ForAll + Patch per row.
UX & Formula Patterns
7. Pass data between screens via Navigate context
// Button OnSelect (Screen A): Navigate(DetailScreen, ScreenTransition.Fade, {locRecord: ThisItem, locMode: "Edit"}) // DetailScreen reads locRecord.Name, locRecord.ID etc. directly
ContextRecord in Navigate sets UpdateContext variables on the target screen — the cleanest inter-screen data transfer.
8. Loading spinner during data fetch
// Button OnSelect: UpdateContext({locLoading: true}); Concurrent( ClearCollect(colData, MyDataSource), Set(gblConfig, LookUp(Config, Key = "App")) ); UpdateContext({locLoading: false}); // Spinner Visible: locLoading
Toggle locLoading to show/hide a spinner while data loads in the background.
9. With() to avoid repeated expensive LookUp
// Instead of two LookUp calls: With({emp: LookUp(Employees, ID = Gallery1.Selected.ID)}, emp.Name & " – " & emp.Department & " – " & emp.Email)
With() evaluates the LookUp once and aliases the result — cheaper and more readable than repeating.
10. Dynamic label color by status (Switch + RGBA)
// Label Color property: Switch(ThisItem.Status, "Active", RGBA(0, 128, 0, 1), "Pending", RGBA(255, 165, 0, 1), "Closed", RGBA(200, 0, 0, 1), RGBA(128, 128, 128, 1))
Switch + RGBA for status-based colour coding — no separate icon or image column needed.
11. Age calculation and deadline countdown
// Age in full years: DateDiff(BirthDatePicker.SelectedDate, Today(), TimeUnit.Years) // Days until deadline (negative = overdue): DateDiff(Today(), DeadlineDatePicker.SelectedDate, TimeUnit.Days)
Always specify TimeUnit explicitly for clarity and to avoid ambiguity in code reviews.
12. Build a comma-separated tag list from multi-select
// Display selected tags from a checkbox gallery: Concat(Filter(colTags, Selected), Name, ", ") // Deep-link pattern: pass ID via Launch, read via Param // Launch(appURL, "id", ThisItem.ID) → Set(gblID, Param("id")) in App OnStart
Concat(Table, Formula, Separator) is the cleanest way to join multi-selected items into a display string.