Mastering Table.Group in Power Query: Unlocking the fifth argument

[Omid_Motamedise]

The Table.Group function in Power Query is an incredible tool for summarizing and grouping data efficiently. Here's a quick overview of its syntax:
Table.Group(table as table, key as any, aggregatedColumns as list, optional groupKind as nullable number, optional comparer as nullable function) as table

While the last two parameters are optional, they can significantly enhance the performance of this function. In this post, I’ll focus on the fifth input, optional comparer as nullable function parameter.

This parameter is typically defined as a function with two input parameters mostly as below:
(x,y)=>Number.From(……..)
At the beginning the x define the first row of the table (Beginning of grouping) and y is determined the every next row in the table. For each y, the Number.From(……..) is evaluated, if the result of this function be 0, y is changed to the next row of table, but if the result of this function be 1, then the group is created and then x is changed to the next row and y determined the rows after x. This process is continued to reach the end of table.


Three different example is provided as bellow (the examples are more clear on the attached images)

📢 If you want to group all rows where the "Region" column is not null, use the following formula:
(x,y)=>Number.From(y[Region]<>null)

📢 To group data every 7 days based on the "Date" column, apply this formula:
(x,y)=>Number.From(y[Date]- x[Date]>=hashtag#duration(7,0,0,0))

📢 If your goal is to group rows where the total cost of each group is less than $130, use this function:
(x,y)=>Number.From(List.Sum(List.Range(Source[Cost], x[Index],y[Index]-x[Index]+1))>130))

[lbendlin]

This seems to be a copy/ripoff of Table.Group: Exploring the 5th element in Power BI and Power Query – (thebiccountant.com)  by @ImkeF 

[Omid_Motamedise]

Thank you for your observation. There's no doubt that thebiccountant.com is an amazing website with inspiring content. However, it's important to note that the combination of Table.Group and Number.From isn't exclusive to any single source. In fact, I was inspired by a video from Mike Girvin that demonstrated a similar approach, which led me to explore this combination in my work.If you look beyond a superficial comparison, you'll notice that the first two examples in my content are introductory and serve to set the stage for the third example. This third example is unique in its approach, as it doesn't simply focus on merging based on starting and ending rows. Instead, it considers all the values within a group to meet a specific condition, which is a distinct application of these functions.

[AlienSx]

Hello, @Omid_Motamedise your 3rd usage of GroupKind.Local is so "unique" that I'd never recommend using it 😁 Have you ever tried it on lets say 5K rows of data? In Excel? Your code hits Source[Cost] list with List.Sum(List.Range(...)) so many times while you did not even bother with buffering it... Well, maybe I am wrong and PQ delegates this job to your source database (if it's your case) but I would prefer doing this with (pre-calculated) buffered list of running total cost.

let
    Source = Table1,
    cost = List.Buffer(Source[cost]), 
    rt_cost = List.Buffer(
        List.Generate(
            () => [i = 0, rt = cost{0}],
            (x) => x[i] < List.Count(cost),
            (x) => [i = x[i] + 1, rt = x[rt] + cost{i}],
            (x) => x[rt]
        )
    ),
    res = Table.Group(
        Source,
        "Index", 
        {"Count", each List.Sum([cost])},
        GroupKind.Local, 
        (s, c) => Number.From((rt_cost{c} - rt_cost{s}) >= 130)
    )
in
    res

 Meanwhile, you may skip column reference in 5th argument (x[Index] or y[Index] in your code) and use just "x" and "y" if you use just single column to group by ("Index") and skip curley braces {} in 2nd argument of Table.Group.

[Omid_Motamedise]

Hi @AlienSx ,

Thanks for pointing out Table.Buffer. I chose not to mention it to keep the solution simpler.

I also appreciate you sharing your solution—it's brilliant to use accumulated values instead of exact ones. However, when I applied your formula to the next set of data, the grouping resulted in sums like 142, 155, 170, 189, and so on. It stopped grouping when the total exceeded 130, whereas we need it to stop before reaching the max value.

9/07/2024 12:00:00 AM	19	0
28/06/2024 12:00:00 AM	55	1
26/07/2024 12:00:00 AM	35	2
30/06/2024 12:00:00 AM	33	3
4/06/2024 12:00:00 AM	57	4
20/05/2024 12:00:00 AM	30	5
10/07/2024 12:00:00 AM	68	6
11/06/2024 12:00:00 AM	55	7
11/06/2024 12:00:00 AM	46	8
19/06/2024 12:00:00 AM	69	9
7/06/2024 12:00:00 AM	63	10
23/05/2024 12:00:00 AM	62	11
2/07/2024 12:00:00 AM	30	12
17/07/2024 12:00:00 AM	34	13
13/08/2024 12:00:00 AM	38	14
20/06/2024 12:00:00 AM	63	15
31/05/2024 12:00:00 AM	36	16
17/07/2024 12:00:00 AM	45	17
10/06/2024 12:00:00 AM	51	18
16/06/2024 12:00:00 AM	69	19
19/07/2024 12:00:00 AM	27	20
16/06/2024 12:00:00 AM	14	21