Grouping

Learn how to group data.

Elements of a collection can be grouped using Collection.GroupBy. Elements of a list can be grouped using List.GroupBy.

These functions behave similarly: each receives the collection or list to group, and then a function that returns a key. Elements that match the key will be joined into an inner collection or list.

For instance, this example groups items of the olympics dataset into nested collections using the key Discipline.

main(country: string, year: int) =
    let
        olympics = Csv.InferAndRead("https://raw-tutorial.s3.eu-west-1.amazonaws.com/summer_olympics.csv"),
        medals = Collection.Filter(olympics, entry -> entry.Country == country and entry.Year == year)
    in
        Collection.GroupBy(medals, e -> e.Discipline)

When given a country and year parameters, it returns a collection of key/groups where the key is the discipline, and the group is a collection of medals (i.e. the dataset rows) for the given country and year..

info

If you want to try this example, you can deploy the following endpoint:

Tutorial 7 - Grouping data in a collection

Learn how to group elements of a collection by key.

Overview

Sample usage:

/tutorial/7-group?country=<string>&year=<int>

For instance, to return a collection of key/group where the key is the discipline, and the group is a collection of medals for the country of Great Britain for the year of 1992:

/tutorial/7-group?country=GBR&year=1992

Code

main(country: string = "GBR", year: int = 1992) =
    let
        olympics = Csv.InferAndRead("https://raw-tutorial.s3.eu-west-1.amazonaws.com/summer_olympics.csv"),
        medals = Collection.Filter(olympics, entry -> entry.Country == country and entry.Year == year)
    in
        Collection.GroupBy(medals, entry -> entry.Discipline)

For instance, when calling it with country GB and year 1992, we obtain:

[
  {
    "key": "Archery",
    "group": [
      {
        "Year": 1992,
        "City": "Barcelona",
        "Sport": "Archery",
        "Discipline": "Archery",
        "Athlete": "TERRY, Simon",
        "Country": "GBR",
        "Gender": "Men",
        "Event": "Individual (Fita Olympic Round - 70M)",
        "Medal": "Bronze"
      },
      {
        "Year": 1992,
        "City": "Barcelona",
        "Sport": "Archery",
        "Discipline": "Archery",
        "Athlete": "HALLARD, Steven",
        "Country": "GBR",
        "Gender": "Men",
        "Event": "Team (Fita Olympic Round - 70M)",
        "Medal": "Bronze"
      },
      {
        "Year": 1992,
        "City": "Barcelona",
        "Sport": "Archery",
        "Discipline": "Archery",
        "Athlete": "PRIESTMAN, Richard John",
        "Country": "GBR",
        "Gender": "Men",
        "Event": "Team (Fita Olympic Round - 70M)",
        "Medal": "Bronze"
      },
      {
        "Year": 1992,
        "City": "Barcelona",
        "Sport": "Archery",
        "Discipline": "Archery",
        "Athlete": "TERRY, Simon",
        "Country": "GBR",
        "Gender": "Men",
        "Event": "Team (Fita Olympic Round - 70M)",
        "Medal": "Bronze"
      }
    ]
  },
  {
    "key": "Athletics",
    "group": [
      {
        "Year": 1992,
        "City": "Barcelona",
        "Sport": "Athletics",
        "Discipline": "Athletics",
        "Athlete": "CHRISTIE, Linford",
        "Country": "GBR",
        "Gender": "Men",
        "Event": "100M",
        "Medal": "Gold"
      },
      {
        "Year": 1992,
        "City": "Barcelona",
        "Sport": "Athletics",
        "Discipline": "Athletics",
        "Athlete": "AKABUSI, Kriss Kezie Uche",
        "Country": "GBR",
        "Gender": "Men",
        "Event": "400M Hurdles",
        "Medal": "Bronze"
      },
      ...
      ...
      ...
      {
        "Year": 1992,
        "City": "Barcelona",
        "Sport": "Athletics",
        "Discipline": "Athletics",
        "Athlete": "BACKLEY, Steve",
        "Country": "GBR",
        "Gender": "Men",
        "Event": "Javelin Throw",
        "Medal": "Bronze"
      }
    ]
  },
  {
    "key": "Boxing",
    "group": [
      {
        "Year": 1992,
        "City": "Barcelona",
        "Sport": "Boxing",
        "Discipline": "Boxing",
        "Athlete": "REID, Robin David",
        "Country": "GBR",
        "Gender": "Men",
        "Event": "67 - 71KG (Light-Middleweight)",
        "Medal": "Bronze"
      }
    ]
  },
  ...
  ...
  ...
]