cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rayprivate Frequent Visitor
Frequent Visitor

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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

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"

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

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

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"

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
rayprivate Frequent Visitor
Frequent Visitor

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

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!