Power Query to Transform Header

[legalhustler]

Column1	Column2	Column3	Column4	Column5	Column6
Name	ID	Begin Year	End Year	Sub	Main
null	024	2015	2016	null	0128
GL	Account	Amount	Debit-Credit	Beginning-Ending
100110	70000003	$500	D	E
123456	60000004	$100	C	B
Name	ID	Begin Year	End Year	Sub	Main
null	024	2019	2020	null	0130
654321	90000001	$200	C	B
987654	80000002	$300	D	E

In Power Query the main headers for this table is in bold (black color) followed by values underneath it, but there are sub-headers in red which I would like to transform in column format so that the Begin Year, End Year and Main goes down a column until the next sub-header is found. How can I make this transformation?

 

[legalhustler]

no, it doesn't
so show proper FULL expected result

Post # 3 is the full expected result. I don't know what else to show. Post # 1 shows the example and Post #3 shows the output I want to achieve. If you take Post #3 then look at Post #1 you can see how those three sub-header columns (Begin Year, End Year, and Main) align with each transaction. The main column headers is in bold but I also need those three fields (I'm referring to them as sub-headers) from above it to be included in a tabular form going down for each transaction until the next sub-header is found.

So Begin Year 2015 End Year 2016 and Main 0128 belongs only to the two transactions below it and Begin Year 2019 End Year 2020 and Main 0130 belongs only to the last two transactions below it.

 

[sandy666]

is that correct?

Begin Year	End Year	Main	GL	Account	Amount	Debit-Credit	Beginning-Ending
2015	2016	128	100110	70000003	$500	D	E
2015	2016	128	654321	90000001	$200	C	B
2019	2020	130	987654	80000002	$300	D	E
2019	2020	130	123456	60000004	$100	C	B

 

[sandy666]

ignore previous post
here is a proper table (I hope)

Begin Year	End Year	Main	GL	Account	Amount	Debit-Credit	Beginning-Ending
2015	2016	128	100110	70000003	$500	D	E
2015	2016	128	123456	60000004	$100	C	B
2019	2020	130	654321	90000001	$200	C	B
2019	2020	130	987654	80000002	$300	D	E

 

[legalhustler]

ignore previous post
here is a proper table (I hope)

Begin Year	End Year	Main	GL	Account	Amount	Debit-Credit	Beginning-Ending
2015	2016	128	100110	70000003	$500	D	E
2015	2016	128	123456	60000004	$100	C	B
2019	2020	130	654321	90000001	$200	C	B
2019	2020	130	987654	80000002	$300	D	E

Yes! That's it. Will just need the number formatting to show leading zero for Main. Can you please provide code. Thx.

 

[sandy666]

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    IF = Table.AddColumn(Source, "Custom", each if [Column1] = "Name" then 0 else if [Column1] = null then 1 else 2),
    Sort0 = Table.Sort(IF,{{"Custom", Order.Ascending}}),
    RC0 = Table.RemoveColumns(Sort0,{"Custom"}),
    FirstN = Table.FirstN(RC0,4),
    Skip0 = Table.Skip(FirstN,1),
    Promote0 = Table.PromoteHeaders(Skip0, [PromoteAllScalars=true]),
    TSC = Table.SelectColumns(Promote0,{"Begin Year", "End Year", "Main"}),
    Skip1 = Table.Skip(RC0,4),
    Sort1 = Table.Sort(Skip1,{{"Column1", Order.Descending}}),
    RC1 = Table.RemoveColumns(Sort1,{"Column6"}),
    Promote1 = Table.PromoteHeaders(RC1, [PromoteAllScalars=true]),
    Tables = Table.AddColumn(Promote1, "Tables", each TSC),
    Expand = Table.ExpandTableColumn(Tables, "Tables", {"Begin Year", "End Year", "Main"}, {"Begin Year", "End Year", "Main"}),
    Sort2 = Table.Sort(Expand,{{"Begin Year", Order.Ascending}, {"GL", Order.Ascending}}),
    AR = Table.AlternateRows(Sort2,2,4,2),
    Reorder = Table.ReorderColumns(AR,{"Begin Year", "End Year", "Main", "GL", "Account", "Amount", "Debit-Credit", "Beginning-Ending"})
in
    Reorder

Begin Year	End Year	Main	GL	Account	Amount	Debit-Credit	Beginning-Ending
2015	2016	0128	100110	70000003	$500	D	E
2015	2016	0128	123456	60000004	$100	C	B
2019	2020	0130	654321	90000001	$200	C	B
2019	2020	0130	987654	80000002	$300	D	E

 

[legalhustler]

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    IF = Table.AddColumn(Source, "Custom", each if [Column1] = "Name" then 0 else if [Column1] = null then 1 else 2),
    Sort0 = Table.Sort(IF,{{"Custom", Order.Ascending}}),
    RC0 = Table.RemoveColumns(Sort0,{"Custom"}),
    FirstN = Table.FirstN(RC0,4),
    Skip0 = Table.Skip(FirstN,1),
    Promote0 = Table.PromoteHeaders(Skip0, [PromoteAllScalars=true]),
    TSC = Table.SelectColumns(Promote0,{"Begin Year", "End Year", "Main"}),
    Skip1 = Table.Skip(RC0,4),
    Sort1 = Table.Sort(Skip1,{{"Column1", Order.Descending}}),
    RC1 = Table.RemoveColumns(Sort1,{"Column6"}),
    Promote1 = Table.PromoteHeaders(RC1, [PromoteAllScalars=true]),
    Tables = Table.AddColumn(Promote1, "Tables", each TSC),
    Expand = Table.ExpandTableColumn(Tables, "Tables", {"Begin Year", "End Year", "Main"}, {"Begin Year", "End Year", "Main"}),
    Sort2 = Table.Sort(Expand,{{"Begin Year", Order.Ascending}, {"GL", Order.Ascending}}),
    AR = Table.AlternateRows(Sort2,2,4,2),
    Reorder = Table.ReorderColumns(AR,{"Begin Year", "End Year", "Main", "GL", "Account", "Amount", "Debit-Credit", "Beginning-Ending"})
in
    Reorder

Begin Year	End Year	Main	GL	Account	Amount	Debit-Credit	Beginning-Ending
2015	2016	0128	100110	70000003	$500	D	E
2015	2016	0128	123456	60000004	$100	C	B
2019	2020	0130	654321	90000001	$200	C	B
2019	2020	0130	987654	80000002	$300	D	E

Did you use the table in post #1 as your Table1 source? I'm getting some errors. Can you provide me your source table so I know I'm not missing anything. Thanks.

 

[sandy666]

Column1	Column2	Column3	Column4	Column5	Column6		Begin Year	End Year	Main	GL	Account	Amount	Debit-Credit	Beginning-Ending
Name	ID	Begin Year	End Year	Sub	Main		2015	2016	0128	100110	70000003	$500	D	E
	24	2015	2016		0128		2015	2016	0128	123456	60000004	$100	C	B
GL	Account	Amount	Debit-Credit	Beginning-Ending			2019	2020	0130	654321	90000001	$200	C	B
100110	70000003	$500	D	E			2019	2020	0130	987654	80000002	$300	D	E
123456	60000004	$100	C	B
Name	ID	Begin Year	End Year	Sub	Main
	24	2019	2020		0130
654321	90000001	$200	C	B
987654	80000002	$300	D	E

 

[Bo_Ry]

Please try

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddedYear = Table.AddColumn(Source, "T", each if Value.Is([Column6], type number) then _ else null),
    Expanded = Table.ExpandRecordColumn(AddedYear, "T", {"Column3", "Column4", "Column6"}, {"Column3.1", "Column4.1", "Column6.1"}),
    FilledDown = Table.FillDown(Expanded,{"Column3.1", "Column4.1", "Column6.1"}),
    RemovedTopRows = Table.Skip(FilledDown,2),
    PromotedHeaders = Table.PromoteHeaders(RemovedTopRows, [PromoteAllScalars=true]),
    Filtered = Table.SelectRows(PromotedHeaders, each ([Column6] = null)),
    RenamedColumns = Table.RenameColumns(Filtered, List.Zip({List.LastN(Table.ColumnNames(Filtered),3), {"Begin Year","End Year","Main"}})),
    SelectColumns = Table.SelectColumns(RenamedColumns,{"Begin Year", "End Year", "Main", "GL", "Account", "Amount", "Debit-Credit", "Beginning-Ending"})
in
    SelectColumns

 

[shaowu459]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    res = Table.Group(
                        Source,
                        "Column1",
                        {
                            "n",
                            each let
                                    a=Table.ToRows(_),
                                    b=List.RemoveRange(List.LastN(a{1},4),2,1),
                                    c=List.FirstN(a{2},5),
                                    d=List.Transform(List.Skip(a,(m)=>m{0} is text),(s)=>b&List.FirstN(s,5))
                                in
                                    #table({"Begin Year","End Year","Main","GL","Account","Amount","Debit-Credit","Beginning-End"},d)
                        },
                        0,
                        (x,y)=>
                                Byte.From(y="Name")
                     )
in
    Table.Combine(res[n])

工作簿1.xlsx
	A	B	C	D	E	F	G	H
1	Column1	Column2	Column3	Column4	Column5	Column6
2	Name	ID	Begin Year	End Year	Sub	Main
3	null	24	2015	2016	null	128
4	GL	Account	Amount	Debit-Credit	Beginning-Ending
5	100110	70000003	$500	D	E
6	123456	60000004	$100	C	B
7	Name	ID	Begin Year	End Year	Sub	Main
8	null	24	2019	2020	null	130
9	654321	90000001	$200	C	B
10	987654	80000002	$300	D	E
11
12	Begin Year	End Year	Main	GL	Account	Amount	Debit-Credit	Beginning-End
13	2015	2016	128	100110	70000003	500	D	E
14	2015	2016	128	123456	60000004	100	C	B
15	2019	2020	130	654321	90000001	200	C	B
16	2019	2020	130	987654	80000002	300	D	E
Sheet1

 

[legalhustler]

Column1	Column2	Column3	Column4	Column5	Column6		Begin Year	End Year	Main	GL	Account	Amount	Debit-Credit	Beginning-Ending
Name	ID	Begin Year	End Year	Sub	Main		2015	2016	0128	100110	70000003	$500	D	E
	24	2015	2016		0128		2015	2016	0128	123456	60000004	$100	C	B
GL	Account	Amount	Debit-Credit	Beginning-Ending			2019	2020	0130	654321	90000001	$200	C	B
100110	70000003	$500	D	E			2019	2020	0130	987654	80000002	$300	D	E
123456	60000004	$100	C	B
Name	ID	Begin Year	End Year	Sub	Main
	24	2019	2020		0130
654321	90000001	$200	C	B
987654	80000002	$300	D	E

The "AR" step where you hard coded the Remove Alternate Rows is not going to work because I provided just a sample data that is easy to work with. My real data has a lot of transactions and it can vary each time my source table changes. This step needs to be dynamic.