Breyta

Deila með

Sort and SortByColumns functions

  • Grein

Applies to: Canvas apps Desktop flows Model-driven apps Power Pages Power Platform CLI

Sorts a table.

Note

PAC CLI pac power-fx commands do not support the SortByColumns function.

Description

The Sort function sorts a table based on a formula.

The formula is evaluated for each record of the table, and the results are used to sort the table. The formula must result in a number, a string, or a Boolean value; it can't result in a table or a record.

Fields of the record currently being processed are available within the formula. Use the ThisRecord operator or simply reference fields by name as you would any other value. The As operator can also be used to name the record being processed which can help make your formula easier to understand and make nested records accessible. For more information, see the examples below and working with record scope.

To sort first by one column and then by another, you embed a Sort formula within another. For example, you can use this formula to sort a Contacts table first by a LastName column and then by a FirstName column: Sort( Sort( Contacts, LastName ), FirstName )

The SortByColumns function can also be used to sort a table based on one or more columns.

The parameter list for SortByColumns provides the names of the columns to sort by and the sort direction per column. Sorting is performed in the order of the parameters (sorted first by the first column, then the second, and so on). Column names are specified as strings, requiring double quotes if directly included in the parameter list. For example, SortByColumns( CustomerTable, "LastName" ).

You can combine SortByColumns with a Drop down or List box control to enable users to select which column to sort by.

In addition to sorting ascending or descending, SortByColumns can sort based on a single column table of values. For example, you can sort record based on the name of a day of the week by supplying [ "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday" ] as the sort order. All records which have Monday" will come first, followed by Tuesday, and so on. Records found that do not appear in the sort table are put at the end of the list.

Tables are a value in Power Apps, just like a string or number. They can be passed to and returned from functions. Sort and SortByColumn don't modify a table; instead they take a table as an argument and return a new table that has been sorted. See working with tables for more details.

Delegation

When possible, Power Apps will delegate filter and sort operations to the data source and page through the results on demand. For example, when you start an app that shows a Gallery control filled with data, only the first set of records will be initially brought to the device. As the user scrolls, additional data is brought down from the data source. The result is a faster start time for the app and access to very large data sets.

However, delegation may not always be possible. Data sources vary on what functions and operators they support with delegation. If complete delegation of a formula isn't possible, the authoring environment will flag the portion that can't be delegated with a warning. When possible, consider changing the formula to avoid functions and operators that can't be delegated. The delegation list details which data sources and operations can be delegated.

If delegation is not possible, Power Apps will pull down only a small set of records to work on locally. Filter and sort functions will operate on a reduced set of records. What is available in the Gallery may not be the complete story, which could be confusing to users.

See the delegation overview for more information.

Syntax

Sort( Table, Formula [, SortOrder ] )

  • Table - Required. Table to sort.
  • Formula - Required. This formula is evaluated for each record of the table, and the results are used to sort the table. You can reference columns within the table.
  • SortOrder - Optional. Specify SortOrder.Descending to sort the table in descending order. SortOrder.Ascending is the default value.

SortByColumns( Table, ColumnName1 [, SortOrder1, ColumnName2, SortOrder2, ... ] )

  • Table - Required. Table to sort.

  • ColumnName(s) - Required. The column names to sort on, as strings.

  • SortOrder(s) - Optional. SortOrder.Ascending or SortOrder.Descending. SortOrder.Ascending is the default. If multiple ColumnNames are supplied, all but the last column must include a SortOrder.

    Note

    For SharePoint and Excel data sources that contain column names with spaces, specify each space as "_x0020_". For example, specify "Column Name" as "Column_x0020_Name".

SortByColumns( Table, ColumnName, SortOrderTable )

  • Table - Required. Table to sort.

  • ColumnName - Required. The column name to sort on, as strings.

  • SortOrderTable - Required. Single column table of values to sort by.

    Note

    For SharePoint and Excel data sources that contain column names with spaces, specify each space as "_x0020_". For example, specify "Column Name" as "Column_x0020_Name".

Examples

For the following examples, we'll use the IceCream data source, which contains the data in this table:

IceCream example.

Formula Description Result
Sort( IceCream, Flavor )

SortByColumns( IceCream, "Flavor" )		 Sorts IceCream by its Flavor column. The Flavor column contains strings, so the table is sorted alphabetically. By default, the sort order is ascending. Alphabetically sorted.
Sort( IceCream, Quantity )

SortByColumns( IceCream, "Quantity" )		 Sorts IceCream by its Quantity column. The Quantity column contains numbers, so the table is sorted numerically. By default, the sort order is ascending. Sorted numerically.
Sort( IceCream, Quantity, SortOrder.Descending )

SortByColumns( IceCream, "Quantity", SortOrder.Descending )		 Sorts IceCream by its Quantity column. The Quantity column contains numbers, so the sort is done numerically. The sort order has been specified as descending. Sorted numerically and descending.
Sort( IceCream, Quantity + OnOrder ) Sorts IceCream by the sum of its Quantity and OnOrder columns for each record individually. The sum is a number, so the table is sorted numerically. By default, the sort order is ascending. Since we are sorting by a formula and not by raw column values, there is no equivalent using SortByColumns. Sorted numerically and ascending.
Sort( Sort( IceCream, OnOrder ), Quantity )

SortByColumns( IceCream, "OnOrder", SortOrder.Ascending, "Quantity", SortOrder.Ascending )		 Sorts IceCream first by its OnOrder column, and then by its Quantity column. Note that "Pistachio" rose above "Vanilla" in the first sort based on OnOrder, and then together they moved to their appropriate place based on Quantity. Sorted with Pistachio above Vanilla.
SortByColumns( IceCream, "Flavor", [ "Pistachio", "Strawberry" ] ) Sorts IceCream by it's Flavor column based on the single column table containing "Pistachio" and "Strawberry". Records which have a Flavor of "Pistachio" will appear first in the result, followed by records that contain "Strawberry". For values in the Flavor column that are not matched, such as "Vanilla", they will appear after the items that were matched. Sorted with Pistachio above Strawberry.

Step by step

To run these examples yourself, create the IceCream data source as a collection:

  1. Add a button, and set its OnSelect property to this formula:
    ClearCollect( IceCream, { Flavor: "Chocolate", Quantity: 100, OnOrder: 150 }, { Flavor: "Vanilla", Quantity: 200, OnOrder: 20 }, { Flavor: "Strawberry", Quantity: 300, OnOrder: 0 }, { Flavor: "Mint Chocolate", Quantity: 60, OnOrder: 100 }, { Flavor: "Pistachio", Quantity: 200, OnOrder: 10 } )
  2. Preview the app, select the button, and then press Esc to return to the default workspace.
  3. Select Collections on the File menu to display the collection that you just created, and then press Esc to return to the default workspace.

Sort

  1. Add another button, and set its OnSelect property to this formula:
    ClearCollect( SortByFlavor, Sort( IceCream, Flavor ) )

    The previous formula creates a second collection, named SortByFlavor, that contains the same data as Ice Cream. However, the new collection contains the data sorted alphabetically by the Flavor column in ascending order.

  2. Press F5, select the new button, and then press Esc.

  3. Select Collections on the File menu to display both collections, and then press Esc to return to the default workspace.

  4. Repeat the last three steps, but change the name of the collection that you want to create, and replace the Sort formula with a different formula from the table of examples earlier in this section that uses Sort.

SortByColumns

  1. Add another button, and set its OnSelect property to this formula:
    ClearCollect( SortByQuantity, SortByColumns( IceCream, "Quantity", SortOrder.Ascending, "Flavor", SortOrder.Descending ) )

    The previous formula creates a third collection, named SortByQuantity, that contains the same data as Ice Cream. However, the new collection contains the data sorted numerically by the Quantity column in ascending order, and then by the Flavor column in descending order.

  2. Press F5, select the new button, and then press Esc.

  3. Select Collections on the File menu to display all three collections, and then press Esc to return to the default workspace.

  4. Repeat the last three steps, but change the name of the collection that you want to create, and replace the SortByColumns formula with a different formula from the table of examples earlier in this section that uses SortByColumns.