Discussion About 2nd Argument in Table.Group Function

The second argument of the Table.Group function is the Key, which can be of any type. This argument becomes especially important when using a custom comparison in the fifth argument.

In this post, I will explore how the second and fifth arguments relate to each other.

The sample data includes Date and Quantity columns.

KeyinTableGroup.xlsx

Download XLSX • 17KB

Text in Key Argument:

let

 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

 Result = Table.Group(Source,"Date",{"New", each _},0, (x,y) => Value.Compare(Date.Month(x), Date.Month(y)))

in

 Result

In this case, the goal is to group the table by month.

We can specify the Date column name as text in the Key argument, and in the custom comparer argument, use x to represent the current value in the Date column and y to represent the next value in the Date column.

List in Key Argument:

let

 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

 Result = Table.Group(Source,{"Date","Quantity"},{"New", each _},0, (x,y) => Byte.From( (Date.Month(x[Date]) <> Date.Month(y[Date])) or (y[Quantity] >= 100 )))

in

 Result

In this case, the goal is to group the table by both month and Quantity.

We can specify the Date and Quantity columns as a list in the Key argument.

This list of two values represents a record, where x refers to the current record, and its columns can be accessed using x[Date] or x[Quantity]. Similarly, y refers to the next record.

Empty List in Key Argument:

let

 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

 Result = Table.Group(Source,{}, {{"Total", each "Total Qty"}, {"Qty", each List.Sum([Quantity])}})

in

 Result

There is no key column to group the table, so this will group entire table in one row.