Power Query to Transform Header

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,105
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Column1Column2Column3Column4Column5Column6
NameIDBegin YearEnd YearSubMain
null02420152016null0128
GLAccountAmountDebit-CreditBeginning-Ending
10011070000003$500DE
12345660000004$100CB
NameIDBegin YearEnd YearSubMain
null02420192020null0130
65432190000001$200CB
98765480000002$300DE

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?
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,105
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Please try


Power Query:
// 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:
Power Query:
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!
 
Last edited:

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,105
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

shaowu459

Well-known Member
Joined
Apr 26, 2018
Messages
534
Office Version
  1. 365
Platform
  1. Windows
Glad to know that you've solved the problem.
 

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,105
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,847
Messages
5,627,239
Members
416,232
Latest member
Ash1432

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top