Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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
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
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!
User | Count |
---|---|
125 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |