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 🙂
Hi Draginko - in the group by function for "Sum of EMP FTE" you have a nested function (i.e. to each statements). When you execute the second each inside the List.Select, PQ will lose visibility of the [e.Cat] from the original table.
{"Sum of EMP FTE", each Number.Round(List.Sum(List.Select([FTE], each if [e.Cat] = "EMP" then [FTE] else 0))), type number},
Try this instead? Note each is syntax sugar for "(_) =>". In the following, I am replacing _ with x and y, so it is clear which table is being used.
{"Sum of EMP FTE", (x) =>
Number.Round(
List.Sum(
Table.SelectRows( x , (y) => [e.Cat] = "EMP" )[FTE]
)
,0)
, type number},
If this leave you with null instead of 0, I you can replace the nulls in a later step.
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} }),