Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Group By and Remove Zero Value Ids

Hi   I have a dataset with an id column a 2 numeric value columns and about 20 categorical columns. Each id has mutliple rows and I would like to group by the id find the ids where the sum of the e...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    perpha not the best, but one of ...

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTJUitWJVkoCsozALJCYrjGYmQxiGsHljeHyJnBpQ4R2XVO4PFBlLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", type text}, {"value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, {{"sum", each List.Sum([value]), type nullable number}, {"all",each _}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [sum] <> 0),
    #"Expanded all" = Table.ExpandTableColumn(#"Filtered Rows", "all", {"value"}, {"value"})
in
    #"Expanded all"
Anonymous's avatar
Anonymous
Not applicable
5 years ago

perpha not the best, but one of ...

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTJUitWJVkoCsozALJCYrjGYmQxiGsHljeHyJnBpQ4R2XVO4PFBlLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", type text}, {"value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, {{"sum", each List.Sum([value]), type nullable number}, {"all",each _}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [sum] <> 0),
    #"Expanded all" = Table.ExpandTableColumn(#"Filtered Rows", "all", {"value"}, {"value"})
in
    #"Expanded all"
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thank you for such a quick and helpful response. That does what I want. When you say perhaps not the best, but one way could you provide any hints on alternative ways that I could perhaps look into in more detail myself.

    Thanks again,

    Bernard

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Anonymous 

      this way of expressing myself is a habit of mine, deriving from the fact that I am still in the learning phase.
      Apart from this, "the best approach" is too strong an expression and in any case it would need to be contextualized, in the sense that "the best approach" could be variable according to the surrounding conditions of the problem.

      however a different approach would be to use
      the table.selectrows function together with others, but I don't know if it is better in some respect: clearer, faster, easier to modify, ...?