Grouping Tables (Multi to Single row)

Overview

Suppose you had data in the following multi-row format:

You wish to group this data by person, and would like to show all cars belonging to each person in a single row.

In this article, we explain how to do this.

Steps

1. Load the data as a query

When loaded into the query editor, the data should look like this:

2. Apply grouping

• Group the data using the following options, on the ‘Person’ column

After grouping, the data should look like this:

3. Modify code

• To show all the cars, belonging to each person, modify the code of the Group By step, to this:

    = Table.Group(#"Changed Type", {"Person"}, {{"Cars", each Text.Combine(List.Sort(_[Car], Order.Ascending), ", "), type text}})

All cars belonging to each person should now show in ascending alphabetical order, separated by a comma delimiter, as shown below:

• Click ‘Close & Load’ to return the results to the worksheet, and you should be done.