A Copilot Studio developer is building an agent that retrieves a collection of employee records from a Dataverse table. The agent needs to display a formatted summary listing only employees whose department is 'Engineering' and whose salary exceeds 90000, sorted by last name, and showing only the FullName and Salary columns. The Dataverse table is named 'Employees' with columns: EmployeeID (number), FullName (text), Department (text), and Salary (number). Which Power Fx expression in a Parse value step correctly produces this formatted collection?
Select an answer to reveal the explanation.
Short Explanation and Infographic
Think of building this query like getting dressed: you Filter first (pick the right people), then ShowColumns (put on only what you need), then SortByColumns (arrange by last name). Sorting after ShowColumns works because the sort column is still in the projected set. Option C looks tempting because it nests the same three functions — but it tries to sort a full table and then project, which wastes memory, whereas D projects first then sorts the lean set.
Full explanation below image
Full Explanation
When combining Power Fx table manipulation functions, the composition order matters both for correctness and for performance. The optimal pipeline for this scenario is: Filter → project columns → sort, which minimizes the data in memory at each stage.
Option D is correct. Filter(Employees, Department = "Engineering", Salary > 90000) correctly filters to qualifying rows using multiple conditions within the same Filter call (the comma-separated conditions act as an implicit And). ShowColumns(...) then projects only the FullName and Salary columns, reducing the record set to just what is needed. Finally, SortByColumns(..., "FullName", SortOrder.Ascending) sorts the projected table by last name. Because FullName is included in the ShowColumns projection, it is still available as a sort key. The use of SortOrder.Ascending (the enumeration form) is correct Power Fx syntax.
Option A is incorrect in multiple ways. Filter() is a function that returns a table; you cannot chain it with And as if both are boolean expressions in a condition. And is a boolean operator that combines two true/false values — it cannot combine two function calls that return tables. This expression will produce a type error.
Option B is syntactically closer but incomplete. SortByColumns(Filter(...), "FullName", Ascending) — Ascending alone is not a valid enumeration reference in Power Fx; the correct form is SortOrder.Ascending. Also, this expression does not project columns with ShowColumns, so it would return all four columns (EmployeeID, FullName, Department, Salary) instead of just the two required columns. The filter logic inside is correct, though.
Option C reverses the ShowColumns and SortByColumns order compared to D. In this arrangement, SortByColumns would receive the full (unshown) table and sort it, then ShowColumns would project. While this would functionally produce correct results in most cases, it processes a wider table during the sort step, which is less efficient. More importantly, on the AB-620 exam, option D demonstrates the canonically recommended pipeline: Filter → project → sort. Also note that in C, the outer function is ShowColumns wrapping SortByColumns, meaning the sort happens on the full data first — a logical waste.
Exam tip: Know the Power Fx table function composition model. Filter, ShowColumns, SortByColumns, and AddColumns are the core four. Multiple filter predicates inside one Filter() call are implicitly AND-ed. The SortOrder enum (SortOrder.Ascending, SortOrder.Descending) is always required — bare 'Ascending' is not valid syntax.