Forum Discussion
Power Query Custom Grouping
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.
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] ) )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 (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
5 Replies
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] ) )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.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 (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
thanks,
RD