Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rayprivate
Regular 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
v-yulgu-msft
Employee
Employee

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.

View solution in original post

2 REPLIES 2
v-yulgu-msft
Employee
Employee

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.

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.