Group By and Remove Zero Value Ids

[BernardWilliam]

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

[Anonymous · Accepted solution]

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"

[BernardWilliam]

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]

Hi @BernardWilliam 

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