How would you auto-number items in a group in Power Query?

[arpost]

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?

[Vera_33]

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"

[arpost]

Thanks for the fast reply, @Vera_33. 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

[Vera_33]

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

[arpost]

@Vera_33, 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]

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"

[Vera_33 · Accepted solution]

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]

@Vera_33, 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"

[Vera_33]

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]

@Vera_33, 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!