Distinct function

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

Summarizes records of a table, removing duplicates.

Description

The Distinct function evaluates a formula across each record of a table and returns a one-column table of the results with duplicate values removed. The name of the column is Value.

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.

When used with a data source, this function can't be delegated. Only the first portion of the data source will be retrieved and then the function applied. The result may not represent the complete story. A warning may appear at authoring time to remind you of this limitation and to suggest switching to delegable alternatives where possible. For more information, see the delegation overview.

Syntax

Distinct( Table, Formula )

  • Table - Required. Table to evaluate across.
  • Formula - Required. Formula to evaluate for each record.

Example

  1. Insert a Button control, and set its OnSelect property to this formula.

    ClearCollect( CityPopulations,
        { City: "London",    Country: "United Kingdom", Population: 8615000 },
        { City: "Berlin",    Country: "Germany",        Population: 3562000 },
        { City: "Madrid",    Country: "Spain",          Population: 3165000 },
        { City: "Hamburg",   Country: "Germany",        Population: 1760000 },
        { City: "Barcelona", Country: "Spain",          Population: 1602000 },
        { City: "Munich",    Country: "Germany",        Population: 1494000 }
    );
    
  2. Select the button while holding down the Alt key.

    The formula is evaluated and the CityPopulations collection is created which you can show by selecting CityPopulations in the formula bar:

    CityPopulations collection shown in result view.

  3. Insert a Data table control, and set its Items property to this formula:

    Distinct( CityPopulations, Country )
    

    You can view the result of this formula in the formula bar by selecting the entire formula:

    Output from Distinct function shown in result view.

  4. Use the Edit fields link in the data table's properties pane to add the Value column:

    Output from Distinct function shown in data table.

  5. Insert a Label control, and set its Text property to the formula:

    First( Sort( Distinct( CityPopulations, Country ), Value ) ).Value
    

    This formula sorts the results from Distinct with the Sort function, takes the first record from the resulting table with the First function, and extracts the Result field to obtain just the country/region name.

    Output from Distinct function showing the first country/region by name.