Grouping a orginal table with two count aggregation

Hi Applicable88 ,

Try to use List.NonNullCount function.

= Table.Group(#"Changed Type", {"Categories", "Year "}, {{"ID SCAN Count", each List.NonNullCount([ID SCAN]), Int64.Type},{"ID MAN Count",each List.NonNullCount([ID MAN]), Int64.Type}})

The whole M languae:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sSU3PL8pMLVbSUYpMTSxSANKeLgrBzo5+EJYvmBGUmptYlA1S5JmXlq8UqxOt5AjkGBkYWgIpQyNjIKkAx2jSIEFjI0Nc0qampjh0GxmAdBoaG2HIOyG0Gxkb4pA2gukzNzfHJ21qYoxP2sLCAiEdCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).2" = _t, #"(blank).4" = _t, #"(blank).6" = _t, #"(blank).8" = _t, #"(blank).10" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Categories", type text}, {"Year ", Int64.Type}, {"ID SCAN", Int64.Type}, {"ID MAN", Int64.Type}, {"Remarks", type text}, {"Info", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Categories", "Year "}, {{"ID SCAN Count", each List.NonNullCount([ID SCAN]), Int64.Type},{"ID MAN Count",each List.NonNullCount([ID MAN]), Int64.Type}})
in
    #"Grouped Rows"

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.