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 and do a left merge but I’m looking for a way where we use only a single query.