power query with if statments that iterate between rows

Question

im trying to convert some routine excel actions to power query but i just cant find the right way.

basicly what im trying to do is to run an if statment that return value based on the row above. for example the following table:

for this table i want to create new column that counts the number of times the same code is shown so the table will look as follow:

in excel the formula im using is: =if(A2=A1,C2+1,1)

*code column is column A and header is at row 1.

is there a way i can iterate via rows in power query the same way as in excel? thanks!

Possible duplicate of [Index by category in Power BI equivalent to SQL row\_number over partition](https://stackoverflow.com/questions/51438508/index-by-category-in-power-bi-equivalent-to-sql-row-number-over-partition) — Ralph, Nov 13 '18 at 09:33

score 0 · Answer 1 · answered Nov 13 '18 at 09:58

This approach creates a "partition" table for each [code] value, adding an index (count) column, then recombining:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Partition Data" = Table.Group(Source, {"code"}, {{"Partition", each Table.AddIndexColumn(_, "count", 1, 1), type table}}),
    #"Combine Partitions" = Table.Combine(#"Partition Data"[Partition])
in
    #"Combine Partitions"

power query with if statments that iterate between rows

1 Answers1