Power Query Custom Grouping

[Dicken]

Hello, 
Is there a way to manipulate grouping , here I have a subtotal   but need it to belong the correct group , 
so I have created this to help illustrate; 
let
Source = #table(
type table [Item = text, Name = text, Unit = Int64.Type],{{"A", "x", 7},{"A", "y", 7},{"A", "y", 8},
{"B", "x", 2}, {"B", "i", 3} } ),
Group = Table.Group(Source, {"Item"}, {{"Count",  (x)=>
      let gtotal = List.Sum( x[Unit] ) in
                               Table.InsertRows( x, Table.RowCount(x) ,{[Item = "", Name = "Total", Unit = gtotal ] } ) }} ),
                          Sub = Table.Combine( Group [Count] )
                          in Sub

I then grouped locally , split and put back together,   I needed to be able to work on them  independently, 

= List.Transform(
      List.Split(
       Table.Group(Sub, {"Item"}, {{"Count", each _ }},
       GroupKind.Local) [Count],2),(x)=>   Table.Combine(x) )

This all works as I wanted I was just interested in there might be other / better functions or ways to create a custom grouping?
Richard. 

[AntrikshSharma · Accepted solution]

@Dicken You can use the 5th parameter of Table.Group to split this based on the equality of values in Item column.

Table.Group ( 
     Sub,
     "Item", 
     { "T", each _ },
     GroupKind.Local, 
     (x, y) => Byte.From ( x <> "" and y <> "" and x <> y ) 
)

 And since you have the "Total" word in the column Name that can also be used.

Table.Group ( 
     Sub,
     {"Item", "Name"}, 
     { "T", each _ },
     GroupKind.Local, 
     (x, y) => Byte.From ( y[Name] <> "Total" and x[Item] <> y[Item] ) 
)

[Dicken · Accepted solution]

Thanks, I'll have to work through that, not very good with custom comperers, and have not used 
Byte.From,   will go through it. 
I may have questions, 
Richad. 

[Dicken]

Thanks for the help , I've sort of got it, 
 
= Table.Group( Source, {"Item"},
{{"N", each _ }} , 0 ,(x,y)=>
Number.From(
x[Item] <> y[Item] and y [Item] <> null ) )  

I still find it odd  true = new group false adds to same,  so thank you for your explanation. 

[Dicken]

thanks, 

RD

[Dicken · Accepted solution]

Thanks, I'll have to work through that, not very good with custom comperers, and have not used 
Byte.From,   will go through it. 
I may have questions, 
Richad. 

[AntrikshSharma · Accepted solution]

@Dicken (x, y) are are basically (CurrentGroup, CurrentRow), a group starts with the first row and then based on comparsion between x and y or just y with some condition either rows are kept in the CurrentGroup or a new group is started.

If a boolean operation between x and y return True then a new group is created, if it is False then CurrentRow is added to the CurrentGroup.

Byte.From is used because the accepted values by 5th argument are either 0 or 1, I could have used Number.From as well.

Now in your scenario you could use the following code, but I don't know how your data will look like or if you are even interested in a second local grouping, if you're then previous code is fine but if not then probably this should be enough, it just adds a small overhead that later if you don't like you will have to make Item = null for total row.

let
    ...,
    Sub = Table.Combine ( Group[Count] ),
    ReplaceWithNull = Table.ReplaceValue ( Sub, "", null, Replacer.ReplaceValue, { "Item" } ),
    FillDown = Table.FillDown ( ReplaceWithNull, { "Item" } ),
    Group2 = Table.Group ( FillDown, "Item", { "Count", each _ } )
in
    Group2

[AntrikshSharma · Accepted solution]

@Dicken You can use the 5th parameter of Table.Group to split this based on the equality of values in Item column.

Table.Group ( 
     Sub,
     "Item", 
     { "T", each _ },
     GroupKind.Local, 
     (x, y) => Byte.From ( x <> "" and y <> "" and x <> y ) 
)

 And since you have the "Total" word in the column Name that can also be used.

Table.Group ( 
     Sub,
     {"Item", "Name"}, 
     { "T", each _ },
     GroupKind.Local, 
     (x, y) => Byte.From ( y[Name] <> "Total" and x[Item] <> y[Item] ) 
)