Forum Discussion
Transform data like two row with main title and subtitle ?
Hello utgqw
here another approach. It uses List.Generate to create a new column that identifies the main groups. In the Group-function the first row is deleted and a new column with the main-cat is added
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgQCJR0lQwMDpVidaKXE4pQ0INcYGw/CMYFwnIAAyDOC6itOBEvCjClPLQLyTJH1gTixAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Cat = _t, Column1 = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"Cat", type text}, {"Column1", Int64.Type}}), GenerateGroupColumns = List.Generate ( ()=> [RunningTotal = ChangedType[Column1]{0}, Counter=0, Group=1], each [Counter]<= Table.RowCount(ChangedType)-1, (rec)=> [ RunningTotal = if rec[RunningTotal] - ChangedType[Column1]{rec[Counter]+1}< 0 then ChangedType[Column1]{rec[Counter]+1} else rec[RunningTotal] - ChangedType[Column1]{rec[Counter]+1}, Counter = rec[Counter]+1, Group = if rec[RunningTotal] - ChangedType[Column1]{rec[Counter]+1}< 0 then rec[Group]+1 else rec[Group] ], each [Group] ), CreateTable = Table.FromColumns(Table.ToColumns(ChangedType)&{GenerateGroupColumns}, Table.ColumnNames(ChangedType)&{"Group"}), GroupedRows = Table.Group ( CreateTable, {"Group"}, { { "AllRows", (tbl)=> Table.AddColumn(Table.Skip(tbl,1), "Main", each tbl[Cat]{0}) } } ), RemovedOtherColumns = Table.SelectColumns(GroupedRows,{"AllRows"}), ExpandedAllRows = Table.ExpandTableColumn(RemovedOtherColumns, "AllRows", {"Cat", "Column1", "Group", "Main"}, {"Cat", "Column1", "Group", "Main"}) in ExpandedAllRowsCopy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Impactful IndividualHi, utgqw
You can try my code, which currently has a data processing capacity of about 12,000 lines.
// output
let
Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("bc2xDoIwFIXhV2nuzGApAWVTVl2qm3G4loY0kN6EW+JgeHd1qy3z+XL++xs0vcQZn3ZiaOFojGWm2VmGAjpafIC2UuVaJPDkRis0mjFyUm2za0DfR26fMwphsiy+TnQ4/MWl3HyNSKN2Gbn8JnReJLZscqsJ+9Spus7cjZbZ+SGlh2p9fAA=", BinaryEncoding.Base64),Compression.Deflate))),
fx = (lsts)=> List.Transform(List.Skip(lsts), each {lsts{0}{0}}&_),
rows = Table.ToRows(Source)&{{null, null}},
acc = List.Accumulate(
rows,
{null, 0, {}, {}}, //{sub_total, running_total, list_sub_tbls, result}
(s,c)=>
if s{1}+c{1}=2*s{0} then {null, 0, {}, s{3}&{ Table.FromRows(fx(s{2}&{c}), {"Main", "Sub", "Count"}) }}
else {if s{1}=0 or s{0}=null then c{1} else s{0}, s{1}+c{1}, s{2}&{c}, s{3}}
),
result = Table.Combine(acc{3})
in
result
sry i'm newbie where i paste the code?
is it working with data like
HeadA
AAAA1
AAAA2
AAAA3
HeadB
BBBB1
BBBB2
HeadC
CCCC1
CCCC2
CCCC3
CCC4
(sub catalogy not equal) ? thank you
- ziying35
To create a new blank query in Power Query, copy and paste the code into the advanced editor of the query.
It doesn't matter if you can't distinguish between the main categories, as long as each main category in your data has a data summation for its subcategories, as in the example file you provided.
Does your new example only have one column of data? You try to simulate the data as close to the real data as possible, so that we can give you code suggestions to better fit your real data.
Jimmy801Community Champion
Hello utgqw
here another approach. It uses List.Generate to create a new column that identifies the main groups. In the Group-function the first row is deleted and a new column with the main-cat is added
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgQCJR0lQwMDpVidaKXE4pQ0INcYGw/CMYFwnIAAyDOC6itOBEvCjClPLQLyTJH1gTixAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Cat = _t, Column1 = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"Cat", type text}, {"Column1", Int64.Type}}), GenerateGroupColumns = List.Generate ( ()=> [RunningTotal = ChangedType[Column1]{0}, Counter=0, Group=1], each [Counter]<= Table.RowCount(ChangedType)-1, (rec)=> [ RunningTotal = if rec[RunningTotal] - ChangedType[Column1]{rec[Counter]+1}< 0 then ChangedType[Column1]{rec[Counter]+1} else rec[RunningTotal] - ChangedType[Column1]{rec[Counter]+1}, Counter = rec[Counter]+1, Group = if rec[RunningTotal] - ChangedType[Column1]{rec[Counter]+1}< 0 then rec[Group]+1 else rec[Group] ], each [Group] ), CreateTable = Table.FromColumns(Table.ToColumns(ChangedType)&{GenerateGroupColumns}, Table.ColumnNames(ChangedType)&{"Group"}), GroupedRows = Table.Group ( CreateTable, {"Group"}, { { "AllRows", (tbl)=> Table.AddColumn(Table.Skip(tbl,1), "Main", each tbl[Cat]{0}) } } ), RemovedOtherColumns = Table.SelectColumns(GroupedRows,{"AllRows"}), ExpandedAllRows = Table.ExpandTableColumn(RemovedOtherColumns, "AllRows", {"Cat", "Column1", "Group", "Main"}, {"Cat", "Column1", "Group", "Main"}) in ExpandedAllRowsCopy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
JimmyReally nice but it hard for me to understand and apply for work ... thankyou
