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
JulieCooper's avatar
JulieCooper
Frequent Visitor
2 years ago

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?

 

 

 

  • JulieCooper's avatar
    JulieCooper
    Frequent Visitor
    2 years ago

    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's avatar
    AlienSx
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      JulieCooper
      Frequent Visitor
      2 years ago

      Thanks, all working now. 

      • AlienSx's avatar
        AlienSx
        Icon for Super User rankSuper User
        2 years ago

        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's avatar
      JulieCooper
      Frequent Visitor
      2 years ago

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

    • JulieCooper's avatar
      JulieCooper
      Frequent Visitor
      2 years ago

      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.