Forum Discussion

JulieCooper's avatar
JulieCooper
Frequent Visitor
2 years ago
Solved

Index multiple subgroups

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 th...
Tutorial Requests
  • AlienSx's avatar
    AlienSx
    2 years ago

    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
dufoq3's avatar
dufoq3
Community Champion
2 years ago

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