Forum Discussion
Index multiple subgroups
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
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"
Super UserThanks 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?
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
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.
Thanks, all working now.