Table.Group to aggregate values based on another column's value

[Draginko]

I am grouping multiple columns, and in some I will have grouping based on some condition. Trying to figure one first, with the name "Sum of EMP FTE", but I am somehow not able to figure out the correct syntax to make this work. This is my current piece of code:

    #"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", each Number.Round(List.Sum(List.Select([FTE], each if [e.Cat] = "EMP" then [FTE] else 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}
        })

Any idea how to achieve aggregation based on the condition in another column? Was browing for the past hour to figure this out, but no solution worked for me, I am usually getting this error:

Expression.Error: We cannot apply field access to the type Number.
Details:
Value=1
Key=e.Cat

As I would have quite a lot of those columns, I would like to avoid slicing it to separate queries and merging them through many nested joins, so would prefer some way to incorporate it into this grouping step.

Thanks to anyone who will look into this!

[Daryl-Lynch-Bzy]

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.

[Draginko]

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?

[Daryl-Lynch-Bzy]

sorry my mistake.  Table.SelectRows is required.

[Draginko · Accepted solution]

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 @Daryl-Lynch-Bzy 🙂

[Draginko]

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}
        }),