Count the number of occurance from the first row to the current row

[rayprivate]

I would like to add a Custom Column to get the result as follow:

Date                  Item      Custom Column
01/01/2019       AAA         001
02/01/2019       BBB          001
02/01/2019       CCC         002
03/01/2019       DDD        001
03/01/2019       EEE          002

In Excel, my formula in C2 would be: =TEXT(COUNTIF($A$2:A2,A2),"000").

However, as a Power BI beginner I am unsure of how to get the same result using Power Query.

Thank you so much in advance!

[v-yulgu-msft · Accepted solution]

Hi @rayprivate ,

Please refer to below Power Query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ1MAQiJR0lR0dHpVgdJEEjoKCTkxOmoLOzM6qgMVDQxcUFU9DV1VUpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Item = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Item", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date"}, {"all", each Table.AddIndexColumn(_, "Index", 1), type table}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Item", "Index"}, {"all.Item", "all.Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded all",{{"all.Item", "Item"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom Column", each Text.PadStart(Text.From([all.Index]),3,"0")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"all.Index"})
in
    #"Removed Columns"

Best regards,

Yuliana Gu

[rayprivate]

Hi @v-yulgu-msft ,

I was able to apply the code you provided to my set of data. Using Table.AddIndexColumn within Grouped Rows did the trick.

Thank you very much for providing a solution!