Power apps Groupby Function Combobox Selected Value

Question

I have a ComboBox and a Gallery in my Power Apps application. I want to GroupBythe selected values from the ComboBox. This does not work because the Groupby function wants a text literal as the second Parameter.

GroupBy(tablename;ComboBox1.Selected;"grouped")

Does anyone know how to solve this?

I have not found a function to convert ComboBox.Selected.Value to String.

Answer 1

GroupBy cannot take arbitrary string values because it needs to know when the expression is being written (and not when it is being executed) which columns it will group, as it will define the schema (type) of its result.

For example, if we have this collection:

ClearCollect(
  CoffeeShops,
  { Country: "USA", StateOrProvince: "WA", City: "Seattle", NumberOfCoffeeShops: 153 },
  { Country: "USA", StateOrProvince: "WA", City: "Bellevue", NumberOfCoffeeShops: 34 },
  { Country: "USA", StateOrProvince: "WA", City: "Spokane", NumberOfCoffeeShops: 27 },
  { Country: "USA", StateOrProvince: "NY", City: "New York City", NumberOfCoffeeShops: 236 },
  { Country: "USA", StateOrProvince: "NY", City: "Albany", NumberOfCoffeeShops: 15 },
  { Country: "USA", StateOrProvince: "NY", City: "Rochester", NumberOfCoffeeShops: 30 },
  { Country: "Canada", StateOrProvince: "BC", City: "Vancouver", NumberOfCoffeeShops: 98 },
  { Country: "Canada", StateOrProvince: "BC", City: "Burnaby", NumberOfCoffeeShops: 10 },
  { Country: "Canada", StateOrProvince: "ON", City: "Toronto", NumberOfCoffeeShops: 105 },
  { Country: "Canada", StateOrProvince: "ON", City: "Windsor", NumberOfCoffeeShops: 20 })

The schema of the result of GroupBy(CoffeeShops, "Country", "Grouped") is a table with 2 columns: "Country" (of type text) and "Grouped" (of type table, with columns "StateOrProvince" and "NumberOfCoffeeShops").

Compare with the schema of the result of GroupBy(CoffeeShops, "StateOrProvince", "Grouped") is a table with 2 columns: "StateOrProvince" (of type text) and "Grouped" (of type table, with columns "Country" and "NumberOfCoffeeShops").

This "strongly typing" of the language is what allows, for example, an app to use that expression as the Items property of a gallery, and reference ThisItem.Country or Sum(ThisItem.Grouped, NumberOfCoffeeShops) in the first case - in the second case it would not work (there's no ThisItem.Country).

If you know all the options beforehand, you can use some of the table shaping functions to convert the result of both GroupBy calls into a common schema, so that you will be able to use it. For example, with an expression similar to the one below:

Switch(
  ComboBox1.Selected.Value,
  "Country",
    RenameColumns(
      GroupBy(CoffeeShops, "Country", "Grouped"),
      "Country", "GroupValue"),
  "StateOrProvince",
    RenameColumns(
      GroupBy(CoffeeShops, "StateOrProvince", "Grouped"),
      "StateOrProvince", "GroupValue"))

The result of this expression is a table with a column called 'GroupValue' and a column called 'Grouped'. With that I can use a gallery with two labels:

• ComboBox1.Selected.Value & ": " & ThisItem.GroupValue
• "Total of coffee places: " & Sum(ThisItem.Grouped, NumberOfCoffeeShops)

Which would look somewhat like the image below:

Or with another selection: