Powerquery running count by group

Question

I am creating a report where I do have two columns example of the table is below:

DocumentNo  ReceiptNo
doc1        Rec1
doc2        Rec2
doc3        rec3
doc1        rec4
doc3        rec5
doc3        rec6

I need to add a column in between where it does give me a running count of the rows grouped by document no

DocumentNo  Helper ReceiptNo
doc1        1      Rec1
doc2        1      Rec2
doc3        1      rec3
doc1        2      rec4
doc3        2      rec5
doc3        3      rec6

it is very easy to do so in Excel itself but I want to do it in Powerquery or SQL level.

Aleksei Zhigulin Aleksei Zhigulin · Accepted Answer · 2019-08-16T13:53:46

In PQ it's quite easy too:

let
    Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
    group = Table.Group(Source, {"DocumentNo"}, {"temp", each Table.AddIndexColumn(_, "Helper", 1, 1)}),
    expand = Table.ExpandTableColumn(group, "temp", {"Helper", "ReceiptNo"})
in
    expand

Or, if you need to keep order of rows:

let
    Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
    i = Table.AddIndexColumn(Source, "i", 0, 1),
    group = Table.Group(i, {"DocumentNo"}, {"temp", each Table.AddIndexColumn(_, "Helper", 1, 1)}),
    expand = Table.ExpandTableColumn(group, "temp", {"Helper", "ReceiptNo", "i"}),
    sort = Table.Sort(expand,{"i", 0}),
    final = Table.RemoveColumns(sort,{"i"})
in
    final

Powerquery running count by group

1 Answers