Power Query to Transform Header

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,160
Office Version
  1. 365
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?
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Using the same data, show us exactly what you want your expected results should look like.
 
Upvote 0
Begin YearEnd YearMainGLAccountAmountDebit-CreditBeginning-End
20152016012810011070000003$500DE
20152016012812345660000004$100CB
20192020013065432190000001$200CB
20192020013098765480000002$300DE

That's what the end result should look like from the above example. The Begin Year, End Year and Main should fill down until the next sub-header (which Begin with Name, ID, Begin Year, End Year, Sub and Main). Hope that helps.
 
Upvote 0
maybe post a full expected result
or
this will be enough
GLAccountAmountDebit-CreditBeginning-EndingBegin YearEnd YearMain
98765480000002$300DE2019,20152020,2016130,128
65432190000001$200CB2019,20152020,2016130,128
12345660000004$100CB2019,20152020,2016130,128
10011070000003$500DE2019,20152020,2016130,128
 
Upvote 0
LegalHustler,

Please be sure to post all "Power Query" questions to the "Power BI" forum, and not the "Excel Questions" forum in the future (we have had to move a few for you this week).
 
Upvote 0
maybe post a full expected result
or
this will be enough
GLAccountAmountDebit-CreditBeginning-EndingBegin YearEnd YearMain
98765480000002$300DE2019,20152020,2016130,128
65432190000001$200CB2019,20152020,2016130,128
12345660000004$100CB2019,20152020,2016130,128
10011070000003$500DE2019,20152020,2016130,128
Almost - my post #3 shows the output. The sub-headers on top belong to the transactions below it and the next sub-header belongs to the transactions below that etc. Makes sense? The Begin Year, End Year, and Main should only have one value.
 
Upvote 0
LegalHustler,

Please be sure to post all "Power Query" questions to the "Power BI" forum, and not the "Excel Questions" forum in the future (we have had to move a few for you this week).
Okay will keep that in mind. Whether we're using Power Query for Excel or Power BI Desktop?
 
Upvote 0
Okay will keep that in mind. Whether we're using Power Query for Excel or Power BI Desktop?
Use it for ALL Power BI products. Note the forum description:
Business Intelligence Tools: Power Pivot, Power View, Power Query, DAX Formulas
 
Upvote 0
something like this?
GLAccountAmountDebit-CreditBeginning-EndingBegin YearEnd YearMain
98765480000002$300DE20192020130
98765480000002$300DE20152016128
65432190000001$200CB20192020130
65432190000001$200CB20152016128
12345660000004$100CB20192020130
12345660000004$100CB20152016128
10011070000003$500DE20192020130
10011070000003$500DE20152016128
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

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
Back
Top