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?

 

[alansidman]

Using the same data, show us exactly what you want your expected results should look like.

 

[legalhustler]

Begin Year	End Year	Main	GL	Account	Amount	Debit-Credit	Beginning-End
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

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]

maybe post a full expected result
or
this will be enough

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

 

[Joe4]

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]

maybe post a full expected result
or
this will be enough

GL	Account	Amount	Debit-Credit	Beginning-Ending	Begin Year	End Year	Main
987654	80000002	$300	D	E	2019,2015	2020,2016	130,128
654321	90000001	$200	C	B	2019,2015	2020,2016	130,128
123456	60000004	$100	C	B	2019,2015	2020,2016	130,128
100110	70000003	$500	D	E	2019,2015	2020,2016	130,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]

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?

 

[sandy666]

my post #3 shows the output

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

 

[Joe4]

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]

something like this?

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