Forum Discussion
Table.Group to aggregate values based on another column's value
Oh okay, got it...this one seems to be working:
{"Sum of EMP FTE", each Number.Round( List.Sum( Table.SelectRows( _, each [e.Cat] = "EMP" )[FTE] ), 0 ), type number },Many thanks for your help Anonymous 🙂
Hello, thanks for your input.
But I am probably not geting it, as I am still having some errors:
In the format as you have provided, I am getting this error:
Expression.Error: The import Table.Select matches no exports. Did you miss a module reference?
As I am not able to find Table.Select, I have tried both List.Select, and Table.SelectColumns, but I am still getting errors:
List.Select:
Expression.Error: We cannot convert a value of type Table to type List.
Details:
Value=[Table]
Type=[Type]
Table.SelectColumns:
Expression.Error: We cannot convert a value of type Function to type Text.
Details:
Value=[Function]
Type=[Type]
What am I missing? Can you please elaborate a little bit your suggestion?
sorry my mistake. Table.SelectRows is required.
But then I am getting:
Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?
Here is the full code again:#"Grouped Rows" = Table.Group( #"Reordered Columns", { "MBR", "Month", "BU" }, { {"Sum of FTE", each Number.Round(List.Sum([FTE]),0), type nullable number}, {"Sum of EMP FTE", (x) => Number.Round( List.Sum( Table.SelectRows( x , (y) => [e.Cat] = "EMP" )[FTE] ) ,0) , type number}, {"Headcount", each Table.RowCount(_), Int64.Type}, {"Sum of USD.Comp", each Number.Round(List.Sum([USD.Comp]),0), Currency.Type}, {"Avg of USD.Comp", each Number.Round(List.Average([USD.Comp]),0), Currency.Type}, {"Sum of RCoW", each Number.Round(List.Sum([RCoW]),0), Currency.Type}, {"Avg of RCoW", each Number.Round(List.Average([RCoW]),0), Currency.Type} }),Oh okay, got it...this one seems to be working:
{"Sum of EMP FTE", each Number.Round( List.Sum( Table.SelectRows( _, each [e.Cat] = "EMP" )[FTE] ), 0 ), type number },Many thanks for your help Anonymous 🙂