Grouping a orginal table with two count aggregation

[Applicable88]

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. 

[v-stephen-msft · Accepted solution]

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.

[Applicable88]

@v-stephen-msft 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. 

[ronrsnfld]

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).

[Anonymous]

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"

[Applicable88]

@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?

[Anonymous]

@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