Forum Discussion

Omid_Motamedise's avatar
Omid_Motamedise
Super User
1 year ago

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

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))

 

Tips and Tricks

4 Replies

    • Omid_Motamedise's avatar
      Omid_Motamedise
      Super User
      1 year ago

      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's avatar
        AlienSx
        Super User
        1 year ago

        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.