Forum Discussion
Grouping a orginal table with two count aggregation
Hello,
I tried to use the "Group by" function within PowerQuery, but got two same row counts. So I guess it count all rows, even though I chose "not the empty fields".
My example original table:
| Categories | Year | ID SCAN | ID MAN | Remarks | Info | |||||
| A | 2019 | 123 | ||||||||
| A | 2019 | 321 | ||||||||
| A | 2019 | 555 | ||||||||
| A | 2020 | 3132 | ||||||||
| B | 2019 | 231 | ||||||||
| B | 2022 | 777 | ||||||||
| B | 2022 | 543 | ||||||||
| B | 2022 | 888 |
I want to group the categories and the year together, and have a aggregation how much count of ID Scan or ID MANUAL I got.
That should be the expected result:
| Categories | Year | ID SCAN Count | ID MAN Count | |||
| A | 2019 | 2 | 1 | |||
| A | 2020 | 1 | 0 | |||
| B | 2019 | 1 | 0 | |||
| B | 2022 | 0 | 3 |
Thank you very much in advance.
Best.
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.
6 Replies
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.
let Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUVIAYiMDQ0so09DIGMrCxLE6WLXAsLGRIbFaTE1NibTFyABmuqGxEV49TpjWGBljugi7FiN0s83NzUnVYmqCGXQEtFhYWGBqiQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Categories = _t, #" " = _t, Year = _t, #" .1" = _t, #"ID SCAN" = _t, #" .2" = _t, #"ID MAN" = _t, #" .3" = _t, Remarks = _t, #" .4" = _t, Info = _t]), #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Categories", type text}, {" ", type text}, {"Year", Int64.Type}, {" .1", type text}, {"ID SCAN", Int64.Type}, {" .2", type text}, {"ID MAN", Int64.Type}, {" .3", type text}, {"Remarks", type text}, {" .4", type text}, {"Info", type text}}), #"Rimosse colonne" = Table.RemoveColumns(#"Modificato tipo",{" ", " .1", " .2", " .3", "Remarks", " .4", "Info"}), #"Raggruppate righe" = Table.Group(#"Rimosse colonne", {"Categories","Year"}, {{"id scan count", each List.Count(List.RemoveNulls(_[ID SCAN])), Int64.Type},{"id man count", each List.Count(List.RemoveNulls(_[ID MAN])), Int64.Type}}) in #"Raggruppate righe"Anonymous thanks for the quick reply. I'm not fully getting all steps. Are the blank fields the problem? But I already chosed "count only not empty fields" in the Group by options, why I need to remove nulls?
Applicable88 It is not clear if what I have proposed works but it is not clear or if it does not work and it is not clear why it does not work.
I can't comment on what you did unless you show your code.PS
certainly instead of the two List functions .... I could have used this one that I didn't remember when I wrote the code.
List.NonNullCount(list as list) as number
Maybe a language problem? I do not see an option for Count "not the empty rows" for the Table.Group function. What is the M Code that is generated (see the Advanced Editor or the contents of the formula bar when selecting that step).
