Table Group , Custom comparer

This video from BI Gorilla helps to explain this.

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

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.