Forum Discussion

Applicable88's avatar
Applicable88
Impactful Individual
4 years ago
Solved

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:...
Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
Anonymous
Not applicable
4 years ago
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's avatar
    Applicable88
    Impactful Individual
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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