Forum Discussion
arpost
Post Prodigy
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 exam...
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"
arpostPost Prodigy
Post ProdigyAnonymous, thanks for the response. Perhaps this will help clarify my need a little more. Let's say I have two years of values from 1/1/2019 to 1/1/2021. I'm needing to programatically create a list that numbers off the "month number" but in sequence like an index, so rather than starting over at 12, it would continue to 13, 14, 15, etc. This also needs to restart when changing from one group to another. I've created this list below to illustrate.
Hope this helps.
| Product | Date | Value | ID |
| Food | 1/1/21 | 2000 | 1 |
| Food | 2/1/21 | 1000 | 2 |
| Food | 2/1/21 | 500 | 2 |
| Food | 3/1/21 | 100 | 3 |
| Food | 4/1/21 | 100 | 4 |
| Food | 5/1/21 | 100 | 5 |
| Food | 5/12/21 | 150 | 5 |
| Food | 6/1/21 | 100 | 6 |
| Food | 7/1/21 | 100 | 7 |
| Food | 8/1/21 | 100 | 8 |
| Food | 9/1/21 | 100 | 9 |
| Food | 10/1/21 | 100 | 10 |
| Food | 11/1/21 | 100 | 11 |
| Food | 12/1/21 | 100 | 12 |
| Food | 1/1/22 | 100 | 13 |
| Food | 2/1/21 | 100 | 14 |
| Shoes | 1/1/21 | 1000 | 1 |
| Shoes | 2/1/21 | 500 | 2 |
Syndicate_Admin
Administrator
AdministratorHi @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"