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?

 

[sandy666]

Example should be representative not easy to work with.

so show proper FULL expected result

my post #3 shows the output.

 

[legalhustler]

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

Beautiful! I had to slightly modify the AddedYear step to:

if Value.FromText(Text.Start([Column6],1)) is number then _ else null

in order to better align with my full data scenario. Really cool how the "_" above retrieves the entire row record. Thanks!

 

[legalhustler]

Example should be representative not easy to work with.

No worries - I was able to use Bo_RY's solutions. I learned how to reference an older applied step from your solution. Thanks.

 

[sandy666]

I learned how to reference an older applied step from your solution. Thanks.

Welcome.

 

[shaowu459]

Glad to know that you've solved the problem.

 

[legalhustler]

Glad to know that you've solved the problem.

Thanks. Looks like you went straight to the M code and used variables instead of the interface in your solution. I would probably need to see you do a video to explain what each part is doing lol