Complete function reference for Power Apps Canvas · Interview-ready · Project-ready
| LenINT | Len(Text) → NumberEx: Len("Power Fx") → 8 | Character count of a string.⭐ Len is for strings; CountRows is for tables — frequently confused in interviews. |
| Find | Find(FindText, WithinText [,StartNum]) → NumberEx: Find("@", Email.Text) → 1-based position of @; Blank if not found | Returns 1-based position of a substring within text. Returns Blank if not found. |
| StartsWith | StartsWith(Text, StartText) → BooleanEx: StartsWith(Title.Text, "Mgr") | True if Text begins with StartText. Case-insensitive. |
| EndsWith | EndsWith(Text, EndText) → BooleanEx: EndsWith(FileName, ".pdf") | True if Text ends with EndText. Case-insensitive. |
| LeftINT | Left(Text, NumChars) → TextEx: Left("Power Fx", 5) → "Power" | Returns leftmost N characters. |
| Right | Right(Text, NumChars) → TextEx: Right("Power Fx", 2) → "Fx" | Returns rightmost N characters. |
| MidINT | Mid(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". |
| SplitINT | Split(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. |
| Unicode | Unicode(Text) → NumberEx: Unicode("A") → 65 | Returns the Unicode code point of the first character. |
| Char | Char(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. |
| Lower | Lower(Text) → TextEx: Lower("HELLO") → "hello" | Converts all characters to lowercase. |
| Upper | Upper(Text) → TextEx: Upper("hello") → "HELLO" | Converts all characters to uppercase. |
| Proper | Proper(Text) → TextEx: Proper("power fx") → "Power Fx" | Capitalises the first letter of each word. |
| TrimINT | Trim(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. |
| TrimEnds | TrimEnds(Text) → TextEx: TrimEnds(" Hi there ") → "Hi there" | Strips only leading and trailing spaces; preserves interior spacing. |
| Replace | Replace(Text, StartNum, NumChars, NewText) → TextEx: Replace("Power", 1, 5, "Canvas") → "Canvas" | Replaces characters at a specific position and length. Position-based. |
| SubstituteINT | Substitute(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. |
| PlainText | PlainText(HTMLText) → TextEx: PlainText("<b>Hi</b>") → "Hi" | Strips HTML tags and decodes entities. Use to sanitise RichTextEditor output. |
| EncodeHTML | EncodeHTML(Text) → TextEx: EncodeHTML("<b>") → "<b>" | Encodes HTML special characters for safe rendering in HTML context. |
| EncodeUrl | EncodeUrl(Text) → TextEx: EncodeUrl("hello world") → "hello%20world" | URL-encodes a string. Required before embedding dynamic values in Launch() URLs. |
| TextINT | Text(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. |
| ValueINT | Value(Text [,LanguageTag]) → NumberEx: Value("1,234.56") → 1234.56 | Parses text to a number. Respects locale decimal/thousand separators.⭐ Always wrap TextInput.Text in Value() before arithmetic to avoid type errors. |
| ConcatINT | Concat(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. |
| ConcatenateDEP | Concatenate(Text1, Text2, …) → TextEx: prefer First.Text & " " & Last.Text | Joins text strings. Prefer the & operator — shorter and more readable. |
| IsMatchINT | IsMatch(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. |
| Match | Match(Text, Pattern [,Options]) → RecordEx: Match("AB-123", "\d+").FullMatch → "123" | Returns the first regex match as a record: FullMatch, SubMatches (table), StartMatch (position). |
| MatchAll | MatchAll(Text, Pattern [,Options]) → TableEx: MatchAll("a1 b2", "[a-z]\d") → 2-row table | Returns a table of all regex matches. Each row: FullMatch, SubMatches, StartMatch. |
| Abs | Abs(Number) → NumberEx: Abs(-5) → 5 | Absolute value — removes the sign. |
| RoundINT | Round(Number, Decimals) → NumberEx: Round(3.567, 2) → 3.57 · Round(2.5, 0) → 3 | Rounds half-away-from-zero.⭐ Round(2.5)=3, RoundDown(2.9)=2, RoundUp(2.1)=3 — common interview comparison. |
| RoundUp | RoundUp(Number, Decimals) → NumberEx: RoundUp(2.1, 0) → 3 | Always rounds away from zero regardless of the decimal portion. |
| RoundDown | RoundDown(Number, Decimals) → NumberEx: RoundDown(2.9, 0) → 2 | Always rounds toward zero (truncates toward zero). |
| Ceiling | Ceiling(Number [,Significance]) → NumberEx: Ceiling(4.1) → 5 · Ceiling(7, 5) → 10 | Rounds up to the nearest multiple of Significance (default 1). |
| Floor | Floor(Number [,Significance]) → NumberEx: Floor(4.9) → 4 · Floor(7, 5) → 5 | Rounds down to the nearest multiple of Significance (default 1). |
| Int | Int(Number) → NumberEx: Int(3.9) → 3 · Int(-3.2) → -4 | Rounds toward negative infinity (true floor for all real numbers). |
| Trunc | Trunc(Number [,Decimals]) → NumberEx: Trunc(-3.9) → -3 · Trunc(3.9) → 3 | Truncates toward zero regardless of sign.⭐ Int(-3.2) = -4 but Trunc(-3.2) = -3 — subtle negative-number difference. |
| Mod | Mod(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. |
| Power | Power(Base, Exponent) → NumberEx: Power(2, 10) → 1024 (same as 2^10) | Raises Base to Exponent. Equivalent to the ^ operator. |
| Sqrt | Sqrt(Number) → NumberEx: Sqrt(144) → 12 | Square root. |
| Exp | Exp(Number) → NumberEx: Exp(1) → 2.71828 (e) | Returns e raised to the given power (e^x). |
| Ln | Ln(Number) → NumberEx: Ln(Exp(1)) → 1 | Natural logarithm (base e). Inverse of Exp. |
| Log | Log(Number [,Base]) → NumberEx: Log(1000, 10) → 3 · Log(8, 2) → 3 | Logarithm. Default base is 10 when omitted. |
| Pi | Pi() → NumberEx: Pi() → 3.14159265358979… | Returns the mathematical constant π. |
| Rand | Rand() → NumberEx: Rand() → 0.0 to 0.9999… (recalculates on every evaluation) | Random decimal in [0,1). Recalculates on every formula evaluation — not cached. |
| RandBetween | RandBetween(Low, High) → NumberEx: RandBetween(1, 100) → random integer 1–100 inclusive | Random integer between Low and High inclusive. |
| SumINT | Sum(Table, Formula) or Sum(N1, N2, …) → NumberEx: Sum(Orders, Quantity * Price) · Sum(10, 20, 30) → 60 | Totals 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. |
| Average | Average(Table, Formula) or Average(N1, N2, …) → NumberEx: Average(Scores, Value) | Mean of values. Table form is delegable on Dataverse. |
| MaxINT | Max(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. |
| Min | Min(Table, Formula) or Min(N1, N2, …) → NumberEx: Min(Tasks, DueDate) | Minimum value. Delegable on Dataverse. |
| Count | Count(Table, Formula) → NumberEx: Count(Orders, OrderNum) | Counts rows where the formula evaluates to a numeric (non-blank) value. |
| CountA | CountA(Table, Formula) → NumberEx: CountA(Tasks, Title) | Counts rows where the formula result is non-blank (any type). |
| CountIfINTD | CountIf(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. |
| CountRowsINTND | CountRows(Table) → NumberEx: CountRows(colCart) → number of items in collection | Total 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. |
| StdevP | StdevP(Table, Formula) or StdevP(N1, N2, …) → NumberEx: StdevP(Results, Score) | Population standard deviation of numeric values. |
| VarP | VarP(Table, Formula) or VarP(N1, N2, …) → NumberEx: VarP(Results, Score) | Population variance of numeric values. |
| IfINT | If(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. |
| SwitchINT | Switch(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 && HasID | True 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. |
| IsBlankINT | IsBlank(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. |
| IsEmptyINT | IsEmpty(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. |
| Blank | Blank() → BlankEx: Set(varUser, Blank()) | Returns an explicit blank value. Use to reset variables or clear optional lookup fields. |
| CoalesceINT | Coalesce(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. |
| IsNumeric | IsNumeric(Text) → BooleanEx: IsNumeric("123abc") → false · IsNumeric("42") → true | True if text can be parsed as a valid number. Use for input validation before calling Value(). |
| IsType | IsType(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. |
| Boolean | Boolean(TextOrNumber) → BooleanEx: Boolean("yes") → true · Boolean(0) → false | Parses "true"/"false"/"yes"/"no"/1/0 strings to Boolean. Useful for SharePoint Yes/No fields stored as text. |
| IfErrorINT | IfError(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. |
| IsError | IsError(Value) → BooleanEx: If(IsError(Value(TextInput.Text)), "Not a number", "") | True if the expression produced an error. Use to show contextual validation messages. |
| Error | Error({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. |
| ErrorsINT | Errors(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. |
| NowINT | Now() → 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. |
| TodayINT | Today() → DateEx: Today() → current date at midnight | Returns today's date with no time component. Use for date comparisons and arithmetic. |
| Date | Date(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. |
| Time | Time(Hour, Minute, Second) → TimeEx: Time(9, 30, 0) → 09:30:00 · Add to a Date with + to get DateTime | Constructs a Time value. Add to a Date using the + operator to build a full DateTime. |
| DateTimeValue | DateTimeValue(Text [,LanguageTag]) → DateTimeEx: DateTimeValue("2026-06-01T09:00:00") | Parses a text string into a DateTime. Supports ISO 8601 and locale-specific formats. |
| DateValue | DateValue(Text [,LanguageTag]) → DateEx: DateValue("01/06/2026", "en-GB") | Parses text to a Date. LanguageTag resolves ambiguous formats (mm/dd vs dd/mm). |
| TimeValue | TimeValue(Text [,LanguageTag]) → TimeEx: TimeValue("9:30 AM") | Parses a text string to a Time value. |
| DateAddINT | DateAdd(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. |
| DateDiffINT | DateDiff(Start, End [,Units]) → NumberEx: DateDiff(BirthDate, Today(), TimeUnit.Years) → age in years | Returns the difference between two dates in the given units. Negative if Start > End.⭐ DateDiff(BirthDate, Today(), TimeUnit.Years) is the standard age-calculation pattern. |
| TimeZoneOffset | TimeZoneOffset([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. |
| Day | Day(DateTime) → NumberEx: Day(Today()) → 1–31 | Day of the month component. |
| Month | Month(DateTime) → NumberEx: Month(Today()) → 1–12 (January = 1) | Month number from a date. |
| Year | Year(DateTime) → NumberEx: Year(Today()) → 2026 | 4-digit year from a date. |
| Hour | Hour(DateTime) → NumberEx: Hour(Now()) → 0–23 | Hour component (24-hour clock). |
| Minute | Minute(DateTime) → NumberEx: Minute(Now()) → 0–59 | Minute component from a datetime. |
| Second | Second(DateTime) → NumberEx: Second(Now()) → 0–59 | Second component from a datetime. |
| Weekday | Weekday(DateTime [,StartOfWeek]) → NumberEx: Weekday(Today(), StartOfWeek.Monday) → 1=Mon … 7=Sun | Day of week as a number. StartOfWeek.Monday makes Monday = 1. |
| WeekNum | WeekNum(DateTime [,StartOfWeek]) → NumberEx: WeekNum(Today()) → 1–54 | Week number of the year based on the locale start-of-week. |
| ISOWeekNum | ISOWeekNum(DateTime) → NumberEx: ISOWeekNum(Today()) | ISO 8601 week number. Monday = first day; Week 1 = week containing the year's first Thursday. |
| Calendar.MonthsLong | Calendar.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.MonthsShort | Calendar.MonthsShort() → Table{Value}Ex: Calendar.MonthsShort() → ["Jan", "Feb", …] | Abbreviated month names for the current locale. |
| Calendar.WeekdaysLong | Calendar.WeekdaysLong() → Table{Value}Ex: Calendar.WeekdaysLong() → ["Sunday", "Monday", …] | Full weekday names for the current locale. |
| Calendar.WeekdaysShort | Calendar.WeekdaysShort() → Table{Value}Ex: Calendar.WeekdaysShort() → ["Sun", "Mon", …] | Abbreviated weekday names for the current locale. |
| Clock.AmPm | Clock.AmPm → TextEx: Clock.AmPm → "AM" or "PM" (locale-aware) | Locale-appropriate AM/PM designation string. |
| Clock.AmPmShort | Clock.AmPmShort → TextEx: Clock.AmPmShort → "a" or "p" (abbreviated) | Abbreviated AM/PM string for the current locale. |
| Clock.IsClock24 | Clock.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(). |
| FilterDINT | Filter(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. |
| SearchNDINT | Search(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. |
| LookUpDINT | LookUp(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. |
| SortINT | Sort(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. |
| SortByColumnsD | SortByColumns(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. |
| Shuffle | Shuffle(Table) → TableEx: Shuffle(Questions) | Returns the table in a random order. Always client-side. |
| FirstINT | First(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. |
| Last | Last(Table) → RecordEx: Last(colHistory) | Returns the last record of a table or collection. |
| FirstN | FirstN(Table, N) → TableEx: FirstN(TopProducts, 5) | Returns the first N rows as a table. |
| LastN | LastN(Table, N) → TableEx: LastN(AuditLog, 10) | Returns the last N rows as a table. |
| Index | Index(Table, RowIndex) → RecordEx: Index(Gallery1.AllItems, 3) | Returns the record at a 1-based row index. Useful for custom pagination controls. |
| Sample | Sample(Table, N) → TableEx: Sample(Questions, 10) | Returns N randomly selected rows. Client-side only. Useful for quiz/survey scenarios. |
| ForAllINTND | ForAll(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. |
| SequenceINT | Sequence(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. |
| Table | Table(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. |
| DistinctINT | Distinct(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. |
| GroupBy | GroupBy(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. |
| Ungroup | Ungroup(Table, ColName) → TableEx: Ungroup(grouped, "RegionOrders") | Flattens a grouped table back to a flat table. Inverse of GroupBy. |
| Zip | Zip(Table1, Table2 [,…]) → TableEx: Zip(Names, Scores) → combined by row index | Combines multiple tables by row index into one table of records. Stops at the shortest table. |
| AddColumnsINT | AddColumns(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. |
| DropColumns | DropColumns(Table, ColName [,ColName2, …]) → TableEx: DropColumns(Accounts, "InternalID", "AuditField") | Returns a table with specified columns removed. Useful to reduce payload before Patch. |
| RenameColumns | RenameColumns(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. |
| ShowColumns | ShowColumns(Table, ColName [,ColName2, …]) → TableEx: ShowColumns(Contacts, "FullName", "Email", "Phone") | Returns a table with ONLY the specified columns (whitelist). Inverse of DropColumns. |
| AsType | AsType(Record, TypeName) → RecordEx: AsType(item.Owner, Contacts).FullName | Casts a polymorphic Dataverse lookup to a specific entity type to access its typed columns. |
| IsType | IsType(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. |
| CollectINT | Collect(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. |
| ClearCollectINT | ClearCollect(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. |
| ClearINT | Clear(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. |
| Remove | Remove(Source, Record1 [,Record2, …] [,All]) → VoidEx: Remove(colCart, ThisItem) | Removes specific records from a collection or data source by record reference. All removes all matching. |
| RemoveIf | RemoveIf(Source, Condition [,Condition2, …]) → VoidEx: RemoveIf(colCart, Qty = 0) | Removes all rows matching the condition. Works on both collections and data sources. |
| Update | Update(Collection, OldRecord, NewRecord [,All]) → VoidEx: prefer UpdateIf or Patch instead | Replaces an entire record in a collection. Rarely used in modern apps — prefer UpdateIf or Patch. |
| UpdateIf | UpdateIf(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. |
| PatchINT | Patch(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. |
| DefaultsINT | Defaults(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. |
| Refresh | Refresh(DataSource) → VoidEx: Refresh(Accounts) | Forces the data source to reload from the server, clearing the local cache. |
| ErrorsINT | Errors(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. |
| SubmitFormINT | SubmitForm(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. |
| ResetFormINT | ResetForm(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. |
| NewForm | NewForm(FormControl) → VoidEx: NewForm(EditForm1) | Sets the form to New mode — all fields blank, ready to create a new record. |
| EditForm | EditForm(FormControl) → VoidEx: EditForm(EditForm1) | Switches the form from View mode to Edit mode, making all data card fields editable. |
| ViewForm | ViewForm(FormControl) → VoidEx: ViewForm(EditForm1) | Sets the form to View (read-only) mode. Fields display their values but cannot be edited. |
| NavigateINT | Navigate(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. |
| BackINT | Back([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. |
| Launch | Launch(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. |
| Exit | Exit([Value]) → VoidEx: Exit() | Closes the app. Optional Value is passed back to the embedding context (model-driven / Teams). |
| Download | Download(URL) → VoidEx: Download(Attachment.AbsoluteUri) | Initiates a file download to the device. Works in browser-based canvas apps. |
| NotifyINT | Notify(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. |
| SetINT | Set(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. |
| UpdateContextINT | UpdateContext({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). |
| WithINT | With({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. |
| ResetINT | Reset(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. |
| Select | Select(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. |
| SetFocus | SetFocus(Control) → VoidEx: SetFocus(SearchBox) | Moves keyboard focus to the specified control. Use after navigation or form submission. |
| PDF(Control1 [,Control2, …]) → PDF objectEx: PDF(Screen1) · pass result to Power Automate as attachment | Generates a PDF from one or more controls or a full screen. Pass the result to a flow for email or storage. | |
| RequestHide | RequestHide() → 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()INT | User() → 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. |
| ParamINT | Param(ParameterName) → TextEx: Param("recordId") → value passed via Launch() or deep link URL | Reads a URL or launch parameter passed when the app was opened.⭐ Deep-link pattern: Launch(appURL, "recordId", selectedID) → Param("recordId") on App OnStart. |
| Language | Language() → 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.Connected | Connection.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.name | Host.name → TextEx: Host.name → "Power Apps" · "Microsoft Teams" · "SharePoint" | Identifies the hosting environment. Adapt UI layout or features based on the host. |
| Host.sessionID | Host.sessionID → TextEx: Host.sessionID → unique GUID per session | Unique session identifier. Useful for telemetry, diagnostics logging, and support cases. |
| RGBAINT | RGBA(Red, Green, Blue, Alpha) → ColorEx: RGBA(0, 120, 212, 1) → Microsoft blue · RGBA(255, 0, 0, 0.5) → semi-transparent red | Constructs 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. |
| ColorValue | ColorValue(ColorNameOrHex) → ColorEx: ColorValue("#0078D4") · ColorValue("OrangeRed") | Converts a CSS named color or hex string to a Power Apps Color value. |
| ColorFade | ColorFade(Color, FadeDelta) → ColorEx: ColorFade(RGBA(0,120,212,1), 0.5) → lighter blue · ColorFade(…, -0.3) → darker | Lightens (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.DarkSlateGray | Built-in named color constants. Full CSS color set available as Color.ColorName enumeration. |
| ValueINT | Value(Text [,LanguageTag]) → NumberEx: Value("3.14") → 3.14 · Value("1.234,56", "de-DE") → 1234.56 | Parses text to a floating-point number. LanguageTag handles locale separators.⭐ Required when doing math on any TextInput — text controls return Text type by default. |
| TextINT | Text(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. |
| Boolean | Boolean(TextOrNumber) → BooleanEx: Boolean("yes") → true · Boolean(0) → false · Boolean("TRUE") → true | Parses "true"/"false"/"yes"/"no"/1/0 to Boolean. Useful for SharePoint Yes/No fields stored as text. |
| Float | Float(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. |
| Decimal | Decimal(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. |
| JSONINT | JSON(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. |
| SaveData | SaveData(Collection, Name) → VoidEx: SaveData(colDraft, "formDraft") | Persists a collection to the device's local storage. Survives app restarts. Native mobile apps only. |
| LoadData | LoadData(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. |
| ClearData | ClearData([Name]) → VoidEx: ClearData("formDraft") · ClearData() → removes all saved data | Deletes saved data from local storage by name. Omit Name to clear all saved data. |
| BitAnd | BitAnd(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. |
| BitOr | BitOr(Num1, Num2) → NumberEx: BitOr(12, 10) → 14 (1100 | 1010 = 1110) | Bitwise OR. Use to combine multiple permission flags into a single integer. |
| BitXor | BitXor(Num1, Num2) → NumberEx: BitXor(12, 10) → 6 (XOR) | Bitwise XOR. Toggle individual flags or detect bit-level differences. |
| BitNot | BitNot(Number) → NumberEx: BitNot(0) → -1 (all bits set) | Bitwise NOT — flips all bits (one's complement). |
| BitShiftLeft | BitShiftLeft(Number, Bits) → NumberEx: BitShiftLeft(1, 3) → 8 (multiply by 2³) | Shifts bits left by N positions. Equivalent to multiplying by 2^N. |
| BitShiftRight | BitShiftRight(Number, Bits) → NumberEx: BitShiftRight(8, 3) → 1 (divide by 2³) | Shifts bits right by N positions. Equivalent to integer-dividing by 2^N. |
| AIClassifyINT | Environment.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. |
| AIExtract | Environment.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. |
| AIReplyINT | Environment.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. |
| AISentiment | Environment.AISentiment({Text:…}) → Record{AnalyzedSentiment}Ex: Set(Result, Environment.AISentiment({Text: ReviewText.Text}).AnalyzedSentiment) → "Positive" | Analyses sentiment. Returns "Positive" / "Negative" / "Neutral". |
| AISummarize | Environment.AISummarize({Text:…}) → Record{SummarizedText}Ex: Set(Result, Environment.AISummarize({Text: LongDoc.Text}).SummarizedText) | Summarises long text into a shorter passage. |
| AISummarizeRecordNew | Environment.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. |
| AITranslate | Environment.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. |
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.| Named formula | Name = 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 formulas | A = 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. |
a = b; b = a; is invalid).| Pure UDFINT | FnName(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 UDF | FnName(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. |
{Title:"x", Published:2001} where the type only declares Title fails).| TypeINT | TypeName := 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 type | TableType := 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. |
| RecordOf | RecordOf(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 + UDT | ParseJSON(JsonText, UDT) → RecordEx: ParseJSON(jsonText, BookType) → typed record, no per-field IsType/AsType needed | Passing a user-defined type as ParseJSON's second argument returns a fully typed record directly. |
Customer, OrderLine, InvoiceHeader once and reuse them everywhere instead of repeating inline type specs.| Function | Dataverse | SharePoint 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. |
IfError(
Patch(Accounts, Defaults(Accounts), {Name: txtName.Text, Status: "Active"}),
Notify("Error: " & FirstError.Message, NotificationType.Error)
);
Notify("Account created!", NotificationType.Success);
// Gallery Items (delegates on Dataverse):
Filter(Contacts, StartsWith(FullName, SearchBox.Text) || StartsWith(Email, SearchBox.Text))
// App OnStart:
Concurrent(
ClearCollect(colProducts, Filter(Products, Active = true)),
ClearCollect(colCategories, Distinct(Products, Category)),
Set(gblCurrentUser, LookUp(Employees, Email = User().Email))
);
// ddCategory Items: Distinct(Products, Category)
// ddProduct Items: Filter(Products, Category = ddCategory.Selected.Result)
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)
IfError(
Patch(Orders, colPendingOrders),
Notify("Batch failed: " & FirstError.Message, NotificationType.Error)
);
Clear(colPendingOrders);
// Button OnSelect (Screen A):
Navigate(DetailScreen, ScreenTransition.Fade, {locRecord: ThisItem, locMode: "Edit"})
// DetailScreen reads locRecord.Name, locRecord.ID etc. directly
// Button OnSelect:
UpdateContext({locLoading: true});
Concurrent(
ClearCollect(colData, MyDataSource),
Set(gblConfig, LookUp(Config, Key = "App"))
);
UpdateContext({locLoading: false});
// Spinner Visible: locLoading
// Instead of two LookUp calls:
With({emp: LookUp(Employees, ID = Gallery1.Selected.ID)},
emp.Name & " – " & emp.Department & " – " & emp.Email)
// 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))
// Age in full years:
DateDiff(BirthDatePicker.SelectedDate, Today(), TimeUnit.Years)
// Days until deadline (negative = overdue):
DateDiff(Today(), DeadlineDatePicker.SelectedDate, TimeUnit.Days)
// 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