Index multiple subgroups

[JulieCooper]

Hi, 

I have a source document with columns A,B,C, and D, and I would like to automate the creation of the codes in columns E, F, and G with an Excel power query. My idea was to group and index the headers, then repeat the same for the Subheader and item. The subheader index depends on the headers, ei, each subgroup index restarts at 1.

I tried many variations using group by and adding a dependent index column. Still, I cannot do this with two levels of grouping, and I couldn't find any video of people attempting something similar. 

Safe to assume this method is not what I need, is there another way to achieve this? I am a pretty novice... my head hurts, haha!

I would be grateful for some assistance

[dufoq3]

Hi @JulieCooper, another solution here.

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY/dCoMwDIVfpfTal7DV6kDYwMEuxIvowlbWRejPnn9VhjBQilBCT875krbruEJ0DOjOTuSCBRqRZ7yZ6MFatB+9yHjOV95nO2kB9GJVgKh99NOAnAKN2qSD6/1/6MWi0W9NYPXiSINAOj555m/g/MzmeX6QEEIcJKSUsbZyk2i1R4bOw2C0e76RfGwWRbG/Y5Moy/IgoZRKEA0MU7BRVVWV+MAarev6F+2/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Headers = _t, Subheaders = _t, #"Item Description" = _t, #"Cost Type" = _t]),
    GroupedRows = Table.Group(Source, {"Headers"}, {{"All1", each
        [ a = Table.AddIndexColumn(_, "Header Code", 1, 1, Int64.Type), //Header Code
          b = Table.Group(a, {"Subheaders"}, {{"All2", (x)=> x}}), 
          c = Table.AddIndexColumn(b, "Subheader Code", 1, 1, Int64.Type),
          d = Table.AddColumn(c, "All3", (x)=> Table.FromColumns(Table.ToColumns(x[All2]) & {List.Repeat({x[Subheader Code]}, Table.RowCount(x[All2]))}, Value.Type(x[All2] & #table(type table[Subheader Code=Int64.Type],{})))), //Subheader Code
          e = Table.Combine(d[All3]),
          f = Table.AddColumn(e, "Item Code", (x)=> if List.Contains({null, ""}, x[Item Description]) then 0 else x[Subheader Code], Int64.Type)
        ][f], type table}}),
    CombinedAll1 = Table.Combine(GroupedRows[All1]),
    Ad_CubitCode = Table.AddColumn(CombinedAll1, "Cubit Code", each Text.From([Header Code]) &
Text.PadStart(Text.From([Subheader Code]),2,"0") &
Text.PadStart(Text.From([Item Code]),2,"0") &
[Cost Type], type text)
in
    Ad_CubitCode

[JulieCooper]

Hi @dalien, Thank you for offering some help; I appreciate it. Is this meant to be inserted in the power query advanced Editor? It looks different. I worked out that I would need to replace the name of my table, but the syntax still feels different. 

So far, I have formatted and sorted my table, see code below. 

Apologies if my question seems stupid, I am new to power query.

let
Source = Excel.CurrentWorkbook(){[Name="CostCentreData"]}[Content],
#"Capitalized Each Word" = Table.TransformColumns(Source,{{"Headers", Text.Proper, type text}, {"Subheaders", Text.Proper, type text}, {"Items Description", Text.Proper, type text}}),
#"Sorted Rows" = Table.Sort(#"Capitalized Each Word",{{"Headers", Order.Ascending}, {"Subheaders", Order.Ascending}, {"Items Description", Order.Ascending}}),
#"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"Headers", type text}, {"Subheaders", type text}, {"Items Description", type text}})
in
#"Changed Type"

[AlienSx]

@JulieCooper , watch this

[JulieCooper]

Thanks @AlienSx , that was helpful. I brought up the query, which looks close to what I am trying to achieve.

The indexing is off, though, and I am struggling to understand and try to fix the function. Hopefully, the screenshot below helps to see the issue. Could you help me understand this part?

[AlienSx]

@JulieCooper , where did Cost Type go? My code works when you have first 4 columns (just like on your first screenshot).

Also, those numbers (instead of "00") in resulting columns work as counters during evaluation. I combine them with proper format in the end - when Cubit Code column is created. But first bring Cost Type column back into 4th place and see if code numbers are correct. Then you may transform each column to desired format in the end. 

p.s. even more: my code works when you have only 4 columns. All calculated codes occupy 5th, 6th etc. places. This can be "fixed" if one change my code to work with list of records (instead of list of lists). But you've just changed starting point and I don't see any reason to waste my time on this.  

[JulieCooper]

Thanks, all working now. 

[AlienSx]

@JulieCooper , same approach but using list of records. Now order of columns is irrelevant but sorting is still important. 

let
    s = your_table,
    rows = List.Buffer(Table.ToRecords(s)),
    cost_types = [SC = "1", LB = "2", MT = "3", PL = "4", OT = "5", SM = "6", RV = "7"],
    func = (prev, curr) => 
        curr & 
        [Header Code = prev[Header Code] + Number.From(prev[Headers] <> curr[Headers]),
        Subheader Code = 
            if prev[Headers] = curr[Headers] 
            then prev[Subheader Code] + Number.From(prev[Subheaders] <> curr[Subheaders]) 
            else 1,
        Item Code = 
            if curr[Items Description] = null 
            then 0 
            else 
                if prev[[Headers], [Subheaders]] <> curr[[Headers], [Subheaders]] 
                then 1 
                else prev[Item Code] + 1,
        Cost Type Code = Record.FieldOrDefault(cost_types, curr[Cost Type])], 
    gen = List.Generate(
        () => [
            i = 0, 
            c = rows{0} & 
                [Header Code = 1, 
                Subheader Code = 1, 
                Item Code = if rows{0}[Items Description] = null then 0 else 1, 
                Cost Type Code = Record.FieldOrDefault(cost_types, rows{0}[Cost Type])]
        ],
        (x) => x[i] < List.Count(rows),
        (x) => [i = x[i] + 1, c = func(x[c], rows{i})],
        (x) => x[c]
    ),
    tbl = Table.FromRecords(gen),
    cubit = Table.AddColumn(
        tbl, 
        "Cubit Code", 
        (x) => Text.From(x[Header Code]) & Number.ToText(x[Subheader Code], "00") &
            Number.ToText(x[Item Code], "00") & Text.From(x[Cost Type Code]) & x[Cost Type]
    )
in
    cubit

[JulieCooper]

I can see where my mistake is, I will try again. Thanks again for helping out with this. I am unfamiliar with those functions, but I like to understand how everything works. 

[JulieCooper]

I am very sorry if I upset you; I am trying to figure it out. 

[JulieCooper]

Just to clarify, the source data is sorted and formated

let
Source = Excel.CurrentWorkbook(){[Name="CostCentreDataSet"]}[Content],
#"Sorted Rows" = Table.Sort(Source,{{"Headers", Order.Ascending}, {"Subheaders", Order.Ascending}, {"Items Description", Order.Ascending}}),
#"Capitalized Each Word" = Table.TransformColumns(#"Sorted Rows",{{"Items Description", Text.Proper, type text}, {"Subheaders", Text.Proper, type text}, {"Headers", Text.Proper, type text}}),
#"Changed Type" = Table.TransformColumnTypes(#"Capitalized Each Word",{{"Headers", type text}, {"Subheaders", type text}, {"Items Description", type text}})
in
#"Changed Type"

[AlienSx · Accepted solution]

@JulieCooper if your data is properly sorted then this should work (w/o Table.Group). If not sorted - sort them first. 

let
    s = your_table,
    rows = List.Buffer(Table.ToRows(s)),
    cost_types = [SC = "1", LB = "2", MT = "3", PL = "4", OT = "5", SM = "6", RV = "7"],
    func = (prev, curr) => 
        curr & 
        {prev{4} + Number.From(prev{0} <> curr{0}),
        if prev{0} = curr{0} then prev{5} + Number.From(prev{1} <> curr{1}) else 1,
        if curr{2} = null then 0 else if {prev{0}, prev{1}} <> {curr{0}, curr{1}} then 1 else prev{6} + 1,
        Record.FieldOrDefault(cost_types, curr{3}, null)}, 
    gen = List.Generate(
        () => [i = 0, c = rows{0} & {1, 1, if rows{0}{2} = null then 0 else 1, Record.FieldOrDefault(cost_types, rows{0}{3}, null)}],
        (x) => x[i] < List.Count(rows),
        (x) => [i = x[i] + 1, c = func(x[c], rows{i})],
        (x) => x[c]
    ),
    tbl = Table.FromRows(gen, Table.ColumnNames(s) & {"Header Code", "Subheader Code", "Item Code", "Cost Type Code"}),
    cubit = Table.AddColumn(
        tbl, 
        "Cubit Code", 
        (x) => Text.From(x[Header Code]) & Number.ToText(x[Subheader Code], "00") &
            Number.ToText(x[Item Code], "00") & Text.From(x[Cost Type Code]) & x[Cost Type]
    )
in
    cubit