Forum Discussion
Post ProdigyHow would you auto-number items in a group in Power Query?
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"
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 |
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"
- arpost
Anonymous, thanks for sharing that! This is really close to the solution and may in fact be what I need.
However, I did a little testing with the M code you provided and noticed that the numbering is solely based on month number rather than on the month's number "in data sequence" by which I basically mean that if there are no entries for 2 months, the numbers jump over those rather than continuing.
In the example below, there were no Food entries for the 7th month, so the auto-number skips 7 and goes ahead with 8, which is a problem when needing to create a continuous numbered sequence.
Is it possible to correct this so the auto-number is based on the month's occurence in the list rather than on the literal month number?
Here's the modified M code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdE9CoAwDAXgu3Qu5Me26gW8gGPppuDWwfuDXSxtaETIkveRJS9Gs+V8GGuQoAwjU1kYEU2yHfKL9IVeGANOzaE0p9kE6H+Z780BBu2u2KyYhxVGeVDyRcmJNGCgsdTH8/hFLG2/8nnLxmoprfatpAc=", 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"Hi arpost
I see, here is one way, although I really doubt you can do DAX measure to count YYYYMM directly
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdE9CoAwDAXgu3Qu5Me26gW8gGPppuDWwfuDXSxtaETIkveRJS9Gs+V8GGuQoAwjU1kYEU2yHfKL9IVeGANOzaE0p9kE6H+Z780BBu2u2KyYhxVGeVDyRcmJNGCgsdTH8/hFLG2/8nnLxmoprfatpAc=", 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}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "YYMM", each Date.ToText([Date],"yyyyMM")), #"Removed Other Columns" = Table.Distinct( Table.SelectColumns(#"Added Custom",{"YYMM", "Product"})), #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Product"}, {{"allrows", each _, type table }}), #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([allrows],"ID",1,1)), #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom1",{"Custom"}), indexTable = Table.ExpandTableColumn(#"Removed Other Columns1", "Custom", {"YYMM", "Product", "ID"}, {"YYMM", "Product", "ID"}), #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Product", "YYMM"}, indexTable, {"Product", "YYMM"}, "indexTable", JoinKind.LeftOuter), #"Expanded indexTable" = Table.ExpandTableColumn(#"Merged Queries", "indexTable", {"ID"}, {"ID"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded indexTable",{"YYMM"}) in #"Removed Columns"- arpost
Anonymous, both of those solutions worked quite well! I did have to make some M modifications for my scenario but was able to use them. Thanks for giving two stellar solutions!