Salary variance
Aggregation
FORMULA
VarP(Employees, Salary)
EXAMPLE EXPLANATION
Calculates the population variance of Salary across all employees.
Collect new employee locally
Collections
FORMULA
Collect(colNewEmployees, {Name: "New Hire", Department: "IT", Salary: 50000, IsActive: true})
EXAMPLE EXPLANATION
Collect(colNewEmployees, {Name: "New Hire", Department: "IT", Salary: 50000, IsActive: true}) adds a new record to the collection colNewEmployees, with Name set to "New Hire", Department set to "IT", Salary set to 50000, and IsActive set to true. Collect() is used to add records to a collection — an in-memory, table-like data source that lives locally within the app — and if colNewEmployees doesn't already exist, it's automatically created with a schema inferred from the fields passed in; if it does exist, the new record is simply appended without affecting existing data.
ClearCollect active employees
Collections
FORMULA
ClearCollect(colActiveEmployees, Filter(Employees, IsActive = true))
EXAMPLE EXPLANATION
Loads all active employees into a fresh local collection.
Collect selected row
Collections
FORMULA
Collect(colSelectedEmployees, ThisItem)
EXAMPLE EXPLANATION
Adds the currently selected gallery row to a local collection, e.g. for multi-select.
Clear a collection
Collections
FORMULA
Clear(colSelectedEmployees)
EXAMPLE EXPLANATION
Empties the colSelectedEmployees collection, e.g. to reset a multi-select gallery.
Patch salary update
SharePoint CRUD
FORMULA
Patch(Employees, LookUp(Employees, Email = "john.doe@contoso.com"), {Salary: 68000})
EXAMPLE EXPLANATION
Updates only the Salary field for the employee matching the given Email.
UpdateIf mark department inactive
SharePoint CRUD
FORMULA
UpdateIf(Employees, Department = "Legal", {IsActive: false})
EXAMPLE EXPLANATION
Sets IsActive to false for every employee in the Legal department.
Remove single record
SharePoint CRUD
FORMULA
Remove(Employees, LookUp(Employees, Email = "abcd@contoso.com"))
EXAMPLE EXPLANATION
Deletes the single employee record matching the given Email.
RemoveIf inactive employees
SharePoint CRUD
FORMULA
RemoveIf(Employees, IsActive = false)
EXAMPLE EXPLANATION
Deletes all inactive employee records from the list.
Create new record with Defaults
SharePoint CRUD
FORMULA
Patch(Employees, Defaults(Employees), {Name: "New Hire", Department: "IT", Salary: 50000, JoiningDate: Today(), Email: "new.hire@contoso.com", IsActive: true})
EXAMPLE EXPLANATION
Patch(Employees, Defaults(Employees), {...}) creates a new record in the Employees data source. Defaults(Employees) gives you a blank starting record with the right structure, and then the {...} part fills in the actual values — Name, Department, Salary, JoiningDate, Email, and IsActive. This is how you add a new row directly to a real data source like SharePoint or Dataverse.
Refresh employees list
SharePoint CRUD
FORMULA
Refresh(Employees)
EXAMPLE EXPLANATION
Reloads the Employees data source to pick up changes made by other users.
Add bonus column to filtered list
Table Shaping (Nested)
FORMULA
AddColumns(Filter(Employees, IsActive = true), "Bonus", Salary * 0.1)
EXAMPLE EXPLANATION
Filters to active employees, then adds a calculated Bonus column equal to 10% of Salary.
Shaped table for gallery
Table Shaping (Nested)
FORMULA
ShowColumns(AddColumns(Filter(Employees, Department = "Finance"), MonthlySalary, Salary / 12), Name, MonthlySalary)
EXAMPLE EXPLANATION
Filters to Finance employees, adds a MonthlySalary column, then keeps only Name and MonthlySalary.
Renamed and filtered columns
Table Shaping (Nested)
FORMULA
RenameColumns(ShowColumns(Filter(Employees, IsActive = true), Name, Designation), Designation, JobTitle)
EXAMPLE EXPLANATION
Filters to active employees, keeps two columns, then renames Designation to JobTitle.
Department roster excluding email
Table Shaping (Nested)
FORMULA
DropColumns(Filter(Employees, Department = "Sales"), Email, Notes)
EXAMPLE EXPLANATION
Filters to Sales employees and removes the Email and Notes columns from the result.
Top 5 earners with bonus
Table Shaping (Nested)
FORMULA
FirstN(AddColumns(SortByColumns(Employees, "Salary", SortOrder.Descending), Bonus, Salary * 0.1), 5)
EXAMPLE EXPLANATION
Sorts all employees by Salary descending, adds a Bonus column, then keeps only the top 5.
Group employees by department
Table Shaping
FORMULA
GroupBy(Employees, Department, DeptGroup)
EXAMPLE EXPLANATION
Groups all employees into subtables nested under DeptGroup, one group per Department.
Distinct department list
Table Shaping
FORMULA
Distinct(Employees, Department)
EXAMPLE EXPLANATION
Returns a one-column table of unique Department values.
Apply department-wide raise
ForAll Iteration
FORMULA
ClearCollect(colITEmployees,Filter(Employees,Department="IT"));ForAll(colITEmployees,Patch(Employees,LookUp(Employees,ID=colITEmployees[@ID]),{Salary:123456}))
EXAMPLE EXPLANATION
This formula updates the salary of every IT employee to 123456 in two steps: first, it copies all IT department employees into a separate collection called colITEmployees (so you're not looping over the live data source directly), then it loops through that collection and uses Patch() to find each matching employee back in Employees and update their Salary to 123456.
Build CSV-like text with ForAll and Concat alternative
ForAll Iteration
FORMULA
Concat(Employees, Name & ",")
EXAMPLE EXPLANATION
Uses Concat (preferred over ForAll for text-joining) to build a comma-separated list of employee names.
Showing
101 –
120 of
132 formulae