PowerApps 中的 GroupBy 和 Ungroup 函数GroupBy and Ungroup functions in PowerApps

记录进行分组和取消分组。Groups and ungroups records of a table.

说明Description

GroupBy 函数返回一个表,其中包含根据一个或多个中的值归组的记录。The GroupBy function returns a table with records grouped together based on the values in one or more columns. 同组中的记录将置于单个记录中,同时添加一个列,用于保留一个嵌套的剩余列的表。Records in the same group are placed into a single record, with a column added that holds a nested table of the remaining columns.

Ungroup 函数逆向执行 GroupBy 过程。The Ungroup function reverses the GroupBy process. 此函数返回一个表,该表将组在一起的记录拆分成单个记录。This function returns a table, breaking into separate records any records that were grouped together.

可以使用 GroupBy 对记录分组,修改返回的表,然后在修改后的表中使用 Ungroup 对记录取消分组。You can group records by using GroupBy, modify the table that it returns, and then ungroup records in the modified table by using Ungroup. 例如,可以通过以下方法删除一组记录:For example, you can remove a group of records by following this approach:

  • 使用 GroupBy 函数。Use the GroupBy function.
  • 使用 Filter 函数来删除整组记录。Use the Filter function to remove the entire group of records.
  • 使用 Ungroup 函数。Use the Ungroup function.

还可以根据分组来聚合结果:You can also aggregate results based on a grouping:

  • 使用 GroupBy 函数。Use the GroupBy function.
  • AddColumns 函数与 SumAverage 和其他聚合函数配合使用以添加一个新列,即组表的聚合。Use the AddColumns function with Sum, Average, and other aggregate functions to add a new column which is an aggregate of the group tables.
  • 使用 DropColumns 函数删除组表。Use the DropColumns function to drop the group table.

Ungroup 会尝试保留馈送至 GroupBy 的记录的初始顺序。Ungroup tries to preserve the original order of the records that were fed to GroupBy. 这并不总是可行(例如,当初始表包含 空白 记录时)。This isn't always possible (for example, if the original table contains blank records).

表是 PowerApps 中的一个值,与字符串或数字类似。A table is a value in PowerApps, just like a string or a number. 可以指定一个表作为函数的自变量,然后函数可返回一个表。You can specify a table as an argument for a function, and a function can return a table. GroupByUngroup 并不修改表,而是将表视为自变量,然后返回另一个表。GroupBy and Ungroup don't modify a table; instead they take a table as an argument and return a different table. 请参阅使用表,了解更多详情。See working with tables for more details.

语法Syntax

GroupBy( Table, ColumnName1 [, ColumnName2, ... ], GroupColumnName )GroupBy( Table, ColumnName1 [, ColumnName2, ... ], GroupColumnName )

  • Table - 必需。Table - Required. 要分组的表。Table to be grouped.
  • ColumnName(s) - 必需。ColumnName(s) - Required. 作为记录分组依据的表中的列名称。The column names in Table by which to group records. 这些列将变为生成的表中的列。These columns become columns in the resulting table.
  • GroupColumnName - 必需。GroupColumnName - Required. ColumnName 中不包含的记录数据存储的列名称。The column name for the storage of record data not in the ColumnName(s).

    备注

    对于列名称带空格的 SharePoint 和 Excel 数据源,请将每个空格指定为“_x0020_”。For SharePoint and Excel data sources that contain column names with spaces, specify each space as "_x0020_". 例如,将“Column Name”指定为“Column_x0020_Name”。For example, specify "Column Name" as "Column_x0020_Name".

Ungroup( Table, GroupColumnName )Ungroup( Table, GroupColumnName )

  • Table - 必需。Table - Required. 要取消分组的表。Table to be ungrouped.
  • GroupColumnName - 必需。GroupColumnName - Required. 包含使用 GroupBy 函数设置的记录数据的列。The column that contains the record data setup with the GroupBy function.

    备注

    对于列名称带空格的 SharePoint 和 Excel 数据源,请将每个空格指定为“_x0020_”。For SharePoint and Excel data sources that contain column names with spaces, specify each space as "_x0020_". 例如,将“Column Name”指定为“Column_x0020_Name”。For example, specify "Column Name" as "Column_x0020_Name".

示例Examples

创建集合Create a collection

  1. 添加一个按钮,然后设置其 Text 属性,以使该按钮显示初始Add a button, and set its Text property so that the button shows Original.
  2. 将“初始”按钮的 OnSelect 属性设置为下面的公式:Set the OnSelect property of the Original button 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:"Rome", Country:"Italy", Population:2874000}, {City:"Paris", Country:"France", Population:2273000}, {City:"Hamburg", Country:"Germany", Population:1760000}, {City:"Barcelona", Country:"Spain", Population:1602000}, {City:"Munich", Country:"Germany", Population:1494000}, {City:"Milan", Country:"Italy", Population:1344000})ClearCollect(CityPopulations, {City:"London", Country:"United Kingdom", Population:8615000}, {City:"Berlin", Country:"Germany", Population:3562000}, {City:"Madrid", Country:"Spain", Population:3165000}, {City:"Rome", Country:"Italy", Population:2874000}, {City:"Paris", Country:"France", Population:2273000}, {City:"Hamburg", Country:"Germany", Population:1760000}, {City:"Barcelona", Country:"Spain", Population:1602000}, {City:"Munich", Country:"Germany", Population:1494000}, {City:"Milan", Country:"Italy", Population:1344000})

  3. 按 F5,选择“初始”按钮,然后按 Esc。Press F5, select the Original button, and then press Esc.

    刚创建了名为 CityPopulations集合,该集合包含此数据:You just created a collection, named CityPopulations, that contains this data:

  4. 若要显示此集合,请选择“文件”菜单中的“集合”,然后选择 CityPopulations 集合。To display this collection, select Collections on the File menu and then select the CityPopulations collection. 此时显示集合中的前五个记录:The first five records in the collection appear:

分组记录Group records

  1. 添加另一个按钮,然后将其 Text 属性设置为 "Group"Add another button, and set its Text property to "Group".
  2. 将此按钮的 OnSelect 属性设为下面的公式:Set the OnSelect property of this button to this formula:

    ClearCollect( CitiesByCountry, GroupBy( CityPopulations, "Country", "Cities" ) )ClearCollect( CitiesByCountry, GroupBy( CityPopulations, "Country", "Cities" ) )

  3. 按 F5,选择“分组”按钮,然后按 Esc。Press F5, select the Group button, and then press Esc.

    刚创建了一个名为 CitiesByCountry 的集合,其中上一个集合的记录按“国家/地区”列进了分组。You just created a collection, named CitiesByCountry, in which the records of the previous collection are grouped by the Country column.

  4. 若要显示此集合中的前五条记录,请在“文件”菜单中选择“集合”。To display the first five records in this collection, select Collections on the File menu.

  5. 若要显示某个国家/地区内城市的人口,请选择该国家/地区(如德国)的“城市”列中的表图标:To display the populations of cities in a country, select the table icon in the Cities column for that country (for example, Germany):

对记录进行筛选和取消分组Filter and ungroup records

  1. 添加另一个按钮,然后设置其 Text 属性,以使该按钮显示“筛选”。Add another button, and set its Text property so that the button shows "Filter".
  2. 将此按钮的 OnSelect 属性设为下面的公式:Set the OnSelect property of this button to this formula:

    ClearCollect( CitiesByCountryFiltered, Filter( CitiesByCountry, "e" in Country ) )ClearCollect( CitiesByCountryFiltered, Filter( CitiesByCountry, "e" in Country ) )

  3. 按 F5,选择添加的按钮,然后按 Esc。Press F5, select the button that you added, and then press Esc.

    刚创建了第三个集合,名为 CitiesByCountryFiltered,其中仅含名称带“e”的国家/地区(即没有西班牙和意大利)。You just created a third collection, named CitiesByCountryFiltered, that includes only those countries that have an "e" in their names (that is, not Spain or Italy).

  4. 再添加一个按钮,然后设置其 Text 属性,以使该按钮显示“取消分组”。Add one more button, and set its Text property so that the button shows "Ungroup".
  5. 将此按钮的 OnSelect 属性设为下面的公式:Set the OnSelect property of this button to this formula:

    ClearCollect( CityPopulationsUngrouped, Ungroup( CitiesByCountryFiltered, "Cities" ) )ClearCollect( CityPopulationsUngrouped, Ungroup( CitiesByCountryFiltered, "Cities" ) )

    这将导致:Which results in:

聚合结果Aggregate results

可使用分组表执行的其他操作是聚合结果。Something else we can do with a grouped table is to aggregate the results. 在本示例中,将对每个国家/地区的主要城市的人口求和。In this example, we will sum the population of the major cities in each country.

  1. 添加另一个按钮,然后设置其 Text 属性,以便该按钮显示“总和”。Add another button, and set its Text property so that the button shows "Sum".
  2. 将“总和”按钮的 OnSelect 属性设置为下面的公式:Set the OnSelect property of the "Sum" button to this formula:

    ClearCollect( CityPopulationsSum, AddColumns( CitiesByCountry, "Sum of City Populations", Sum( Cities, Population ) ) )ClearCollect( CityPopulationsSum, AddColumns( CitiesByCountry, "Sum of City Populations", Sum( Cities, Population ) ) )

    这将导致:Which results in:

    AddColumns 以基本 CitiesByCountry 集合开始,并添加新的列“城市人口总和”。AddColumns starts with the base CitiesByCountry collection and adds a new column Sum of City Populations. 此列的值将根据公式 Sum( Cities, Population ) 逐行进行计算。This column's values are calculated row-by-row, based on the formula Sum( Cities, Population ). AddColumns 为每行提供城市列的值,而 Sum 将此子表的每行的人口进行加总。AddColumns provides the value of the Cities column (a table) for each row, and Sum adds up the Population for each row of this sub table.

  3. 现在,已得到所需的总和,可以使用 DropColumns 删除子表。Now that we have the sum that we want, we can use DropColumns to remove the sub tables. 修改 OnSelect 属性以使用下面的公式:Modify the OnSelect property to use this formula:

    ClearCollect( CityPopulationsSumOnly, DropColumns( CityPopulationsSum, "Cities" ) )ClearCollect( CityPopulationsSumOnly, DropColumns( CityPopulationsSum, "Cities" ) )

    这将导致:Which results in:

    请注意,无需对此表执行取消分组。Note that we did not need to ungroup this table.