How to do aggregations while preserving the same number of rows?

Question

My input table is the two orange columns and I'm trying to create the three blue ones :

• The column ND_ID is the index of each group of ids
• The column CNT_ID is the length of each group of ids
• The column SUM_VAL is the sum of vals for each group of ids

For the moment, I was able to create the first one N_ID :

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText("i45WclTSUTJSitWBsIzhLHMwywnIMoWzDMEsZyDLQik2FgA=", BinaryEncoding.Base64),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [ID = _t, VAL = _t]
  ),
  Types = Table.TransformColumnTypes(Source, {{"ID", type text}, {"VAL", Int64.Type}}),
  Group = Table.Group(
    Types,
    {"ID"},
    {{"ALL", each _, type table [ID = nullable text, VAL = nullable number]}}
  ),
  Index = Table.AddColumn(Group, "COL", each Table.AddIndexColumn([ALL], "N_ID", 1)),
  Expand = Table.ExpandTableColumn(Index, "COL", {"N_ID"}, {"N_ID"}),
  Delete = Table.RemoveColumns(Expand, {"ALL"})
in
  Delete

Can you guys help me create the two other ones ?

I know we can duplicate the query, do the aggregations and then a left merge but I'm looking for a way where we use only a single query.

Answer 1

Try:

---

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText("i45WclTSUTJSitWBsIzhLHMwywnIMoWzDMEsZyDLQik2FgA=", BinaryEncoding.Base64),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [ID = _t, VAL = _t]
  ),
  Types = Table.TransformColumnTypes(Source, {{"ID", type text}, {"VAL", Int64.Type}}),
  Group = Table.Group(Types, {"ID"}, {{"ALL", each _, type table [ID=nullable text, VAL=nullable number]}, {"CNT_ID", each Table.RowCount(_), Int64.Type}, {"SUM_VAL", each List.Sum([VAL]), type nullable number}}),
  Index = Table.AddColumn(Group, "COL", each Table.AddIndexColumn([ALL], "N_ID", 1)),
  Expand = Table.ExpandTableColumn(Index, "COL", {"VAL", "N_ID"}, {"VAL", "N_ID"}),
  Delete = Table.RemoveColumns(Expand,{"ALL"}),
  Reorder = Table.ReorderColumns(Delete,{"ID", "VAL", "N_ID", "CNT_ID", "SUM_VAL"})
in
  Reorder

---