Forum Discussion

arpost's avatar
arpost
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

How 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...
Need Help
  • Anonymous's avatar
    Anonymous
    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"

     

Anonymous's avatar
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"

 

arpost's avatar
arpost
Icon for Post Prodigy rankPost Prodigy
5 years ago

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!