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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,158
Office Version
  1. 365
Platform
  1. Windows
Using the same data, show us exactly what you want your expected results should look like.
 

legalhustler

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

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,641
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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).
 

legalhustler

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

legalhustler

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

ADVERTISEMENT

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?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,641
Office Version
  1. 365
Platform
  1. Windows
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
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
something like this?
GLAccountAmountDebit-CreditBeginning-EndingBegin YearEnd YearMain
98765480000002$300DE20192020130
98765480000002$300DE20152016128
65432190000001$200CB20192020130
65432190000001$200CB20152016128
12345660000004$100CB20192020130
12345660000004$100CB20152016128
10011070000003$500DE20192020130
10011070000003$500DE20152016128
 

Watch MrExcel Video

Forum statistics

Threads
1,127,504
Messages
5,625,186
Members
416,077
Latest member
SJSB

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