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 each of the numeric values is zero and remove these ids from the dataset.

 

I have gotten this far to group by the ids and I know how to find which have zero value, but is there a way to do this, remove the unwanted ids and repeat the process with "value2" within the same query? I tried to add mutliple let statements but it did not work. If I have to create multiple queries to do this what is the best approach?

 

Thanks,

Bernard

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"

3 Replies

  • 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, ...?