Forum Discussion
Grouping rows with dynamic columns and different criteria
Hello,
I have a table that I would like to summarize.
So I would like to group by "IdBatch" and "BTCode", and:
1) summarize data by column for all "LColumns" (a list of column names)
2) keep the min for start time
3) keep the max for end time
The first part of my code below works, so I can sum all LColumns, but I can't understand how to place the additional min/max calculations.... so running the code below will give an error "We cannot convert a value of type List to type Text."
Thanks a lot for your help!
Valeria
#"Grouped Rows2" = Table.Group(#"Pivoted Column", {"IdBatch", "BTCode"},
{
{
List.Transform(
LColumns,
(l)=> {l, each List.Sum(Table.Column(_, l)), type nullable number}
)},
{"Start Time", each List.Min([StartTimeLTZ]), type nullable datetime}
}
),
OK
#"Grouped Rows2" = Table.Group(#"Pivoted Column", {"IdBatch", "BTCode"},
List.Transform(
LColumns,
(l)=> {l, each List.Sum(Table.Column(_, l)), type nullable number}
)
& {
{"Start Time", each List.Min([StartTimeLTZ]), type nullable datetime} ,
{"End Time", each List.Max([EndtTimeLTZ]), type nullable datetime}
}
),Stéphane
6 Replies
OK
#"Grouped Rows2" = Table.Group(#"Pivoted Column", {"IdBatch", "BTCode"},
List.Transform(
LColumns,
(l)=> {l, each List.Sum(Table.Column(_, l)), type nullable number}
)
& {
{"Start Time", each List.Min([StartTimeLTZ]), type nullable datetime} ,
{"End Time", each List.Max([EndtTimeLTZ]), type nullable datetime}
}
),Stéphane
Thank you! This works 🙂 for me to understand: so with the "&" you are combining lists right? I had tried List.Combine before but that did not work.... can you just quickly elaborate on the logic? Thanks!
Yes,
{{"A", "B"}} & {{"C", "D"}, {"E", "F"}} = List.Combine({{{"A", "B"}}, {{"C", "D"}, {"E", "F"}}})#"Grouped Rows2" = Table.Group(#"Pivoted Column", {"IdBatch", "BTCode"},
List.Combine({
List.Transform(LColumns,
(l)=> {l, each List.Sum(Table.Column(_, l)), type nullable number}
)
, {
{"Start Time", each List.Min([StartTimeLTZ]), type nullable datetime} ,
{"End Time", each List.Max([EndtTimeLTZ]), type nullable datetime}
}
})
),Stéphane
Thank you! Now I know - I had tried List.Combine but got the brackets wrong... thanks a lot!
Hi,
= Table.Group(#"Pivoted Column", {"IdBatch", "BTCode"},
{
{"Sum_LColumns",
(data) => List.Sum(List.Transform(LColumns, each List.Sum(Table.Column(data, _)))), type nullable number},
{"Start Time",
each List.Min([StartTimeLTZ]), type nullable datetime},
{"End Time",
each List.Max([EndTimeLTZ]), type nullable datetime}
}
)Stéphane
Hi Stéphane, thank you, sorry I think I did not explain the need well...
The first part of the code is OK:
#"Grouped Rows2" = Table.Group(#"Pivoted Column", {"IdBatch", "BTCode"},
List.Transform(
LColumns,
(l)=> {l, each List.Sum(Table.Column(_, l)), type nullable number}
)),I do want a sum by column of each column in LColumns by IDBatch and BTCode.
What I struggle with is adding the second part with List.Max/List.Min for the dates.... I am not sure how to combine all criteria together.
Thanks!
Kind regards
Valeria