Forum Discussion
Post ProdigyHow would you auto-number items in a group in Power Query?
I have a table that I need to group together so I can number off the values within each group. I know how to use group by, but the problem is I need the index to restart with each group.
For example, let's say I had a table with the following:
| Product | Date | Value |
| Food | 1/1/21 | 2000 |
| Food | 2/1/21 | 1000 |
| Food | 2/1/21 | 500 |
| Shoes | 1/1/21 | 1000 |
| Shoes | 2/1/21 | 500 |
I want the final product to look like this
| Product | Date | Value | ID |
| Food | 1/1/21 | 2000 | 1 |
| Food | 2/1/21 | 1000 | 2 |
| Food | 2/1/21 | 500 | 2 |
| Shoes | 1/1/21 | 1000 | 1 |
| Shoes | 2/1/21 | 500 | 2 |
Anyone have any ideas?
Hi arpost
I am not sure how you will write your DAX, but based on your sample, here is one way to add the column you want in M
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsvPT1HSUTIw1AciIwMjQyDHyMDAQClWB0XSCCZpiE/SFFPOGEkjupwJHjlTouRMMeTM8OgzxyNngUfOErecoQEeOUM8ckZ47APrM8IuZ4QuF5yRn1qMHoXwWEKWRY2mWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Date = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}), MinYear = Date.Year( List.Min( #"Changed Type"[Date])), #"Added Custom" = Table.AddColumn(#"Changed Type", "ID", each Date.Month([Date])+(Date.Year([Date])-MinYear)*12) in #"Added Custom"
9 Replies
Hi arpost
ID is your index column starts with 1 for each Product? and it is 1,2,3 for Food, not 1,2,2 right?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsvPT1HSUTIw1AciIwMjQyDHyMDAQClWByFphCRpiE/SFCoXnJGfWoxuLFwnXBZDaywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Date = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Date", type date}, {"Value", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Product"}, {{"allrows", each _, type table }}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([allrows],"ID",1,1)), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Product", "Date", "Value", "ID"}, {"Product", "Date", "Value", "ID"}) in #"Expanded Custom"
arpostThanks for the fast reply, Anonymous. Actually, it is correct that the numbering is 1,2,2 for the "Food" category because I'm needing to create a numbered sequence for all months (0-6, 7-12, 13-24, 25+) for each "group" to track values to targets. In my example with Food, there are 2 actual months while there are 3 Food entries. This will enable me to then display this data in a visual like below:
Product Months Value Food 2 3500 Shoes 1 1500 Hi arpost
So you did not want an Index column, just a column with month number? The visual you want to do is like counting Months? If you have a Calendar table, you can use DAX measure to count it directly based on my understanding