Forum Discussion
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.
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.
Anonymous thank you for providing the pbix. I see that you also didn't use the "group by" button, instead you directly wrote the table.goup function. Through your example I saw my mistake and why I got the same row counts, because I had it as string and not integer, so the funtion doesn't know how to ignore null values.
Thank you so much.
Best.