Forum Discussion
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 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
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
11 Replies
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_CubitCodeJulieCooper 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 cubitHi 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"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?
