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

Anonymous

Not applicable

5 years ago

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

Post Prodigy

5 years ago

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"

Anonymous

Not applicable

5 years ago

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"

Syndicate_Admin

Administrator

5 years ago

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"

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

Forum Discussion

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