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"
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 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!