Table Group , Custom comparer

[Dicken]

Hi, 
I don't know if someone can help with a bit of theory; 

Table.Group comparer function,

I have a table with a column 'Test' which contains x ,y or z , local group.

Table.Group(Source, {"Test"} ,
{{"N", each _ }} ,0, (x, y)=>
Number.From( x [Test] = [Test] ) )

I have seen this explained as 'current' versus 'next' but that does not seem to be the case,
it seems whatever the first value is x y or z is taken and then each subsequent value is compared to it
and if true / 1 is returned a new group is started.

Further here a grouping is based on "Unit" and the sum of each group is no larger than 10 , index start = 0;

Table.Group(Atable, {"Index"}, {{"Count", each _}},
0, (x,y)=> Number.From(List.Sum( List.Range( Atable [Unit] , x[Index] , (y[Index] - x [Index]) +1)) > 10 ))

Can someone help with a bit more information or sites dealing with this matter. Even REG's
PQ how does not seem to have a great deal of info, I don't know if C Webb has something if so I haven't found it.

[jgeddes · Accepted solution]

This video from BI Gorilla helps to explain this.

Custom Comparer Function for Table.Group in Power Query M - BI Gorilla 

[Dicken]

Thank you sorry for not getting back to you sooner. 

[jgeddes · Accepted solution]

This video from BI Gorilla helps to explain this.

Custom Comparer Function for Table.Group in Power Query M - BI Gorilla 

[Dicken]

Thanks, 
RD

[SacheeTh · Accepted solution]

This video seems helps a lot, thanks for sharing @jgeddes 

Seems this may help as well,  what I learnt from the tutorial above.

The Table.Group function in Power Query/M is a powerful but complex function, especially when using your custom comparer logic to group data dynamically. Let me break this down and clarify how the comparer function works, based on the examples provided.

---

Key Concepts in Table.Group

1. Grouping Key: The first argument, {"Test"} or {"Index"}, specifies the column(s) used for grouping. Rows with the same key are grouped together.

2. Operations: The second argument is a list of operations (e.g., {{"N", each _}}) applied to the grouped rows. These operations define what data is kept or summarized in the output table.

3. Comparer Function: The optional comparer function determines when to start a new group based on custom logic. This function takes two arguments:

   • x: The current starting row of a group.
   • y: The next candidate row for the group.

   The comparer returns a boolean (true to end the current group and start a new one, or false to continue adding rows to the current group).

---

I have the same feature comparison option defined in a report, using power query:

DId this in both way 1: Grouping Based on Test Column

Table.Group(Source, {"Test"}, {{"N", each _}}, 0, (x, y) => Number.From(x[Test] = y[Test]))

small xplanation:

• Purpose: This logic checks whether the value in the Test column of the current starting row (x[Test]) matches the Test value in the next candidate row (y[Test]).
• Behavior:
  • If the Test value is the same, Number.From(x[Test] = y[Test]) evaluates to 1 (true), and the row stays in the same group.
  • If the Test value differs, the comparison evaluates to 0 (false), starting a new group.
• Key Insight: The first value in the column establishes the grouping boundary, but the logic doesn't inherently treat it as "current vs. next" — rather, it's just applying the equality condition (x[Test] = y[Test]) iteratively.

---

methd 2: Grouping Based on Unit Sum Constraint

Table.Group(Atable, {"Index"}, {{"Count", each _}}, 0, 
    (x, y) => Number.From(
        List.Sum(List.Range(Atable[Unit], x[Index], (y[Index] - x[Index]) + 1)) > 10
    )
)

Explanation:

• Purpose: Group rows such that the sum of the Unit values within each group does not exceed 10.
• Steps:
  1. Dynamic Range: List.Range extracts a range of values from Atable[Unit], starting at x[Index] and spanning (y[Index] - x[Index]) + 1 rows.
  2. Sum Check: List.Sum computes the sum of the extracted range.
  3. Threshold: The comparer checks if the sum exceeds 10. If true, a new group starts.

Behavior:

• This comparer creates groups dynamically based on the cumulative sum constraint.