Dynamically rearrange rows into one Header column in Power Query

[Syndicate_Admin]

Hello, i have been looking for a viable solution quite some time but unfortunately did not find something appropriate so I have to come and ask you guys.

The situation is as follows - I have a table which does always have quite the same layout with the only difference that my table headers are located on a different Row starting with Column J and from Column J on i can have variable amount of Column-Headers because they are representing Inventory amounts of Calendar-Weeks - so for illustriation it looks sometihing like following

Header A	Header B	Header C....	Header J
				2020-W01	2020-W02	2020-W03	2020-W04
Text A	Text B	Text C etc	Text J	1	2	34	432

Now as i mentioned - it may be that in other tables i will have not 4 but 8 Calendar week columns instead! I am looking for a dynamical way in Power Query to "unify" the first 2 Rows into ONE HEADER COLUMN indifferent if it will be 4 or 8 or "x" columns representing calendar weeks! 

The end result should be something like this

Header A	Header B	Header C	Header J	2020-W1	2020-W2	2020-W3	2020-W4
Text A	Text B	Text C	Text J	1	2	34	432
etc.....

I hope you understand my issue - if there is anything else i could help you with please let me know!

Thanks a lot for your appreciated help!!!

[Syndicate_Admin · Accepted solution]

Hi @padinator ,

Have you tried selecting all of your [Header X] columns and using the Fill Down function on the Transform tab?

This assumes that none of the value cells in these columns will be null.

Pete

[Syndicate_Admin]

Well yes - this was quite the solution - what i did was to Mark the FIRST Columns A-J and FillDown since the cells underneath where "nulls" so that i have all Column Headers in one single Row - then i used this Row as Header Row - I deleted the other 2 Top Rows so that i get a table with Header A - J following one Header Column per calendar week and finally unpivot the table using (Other) Thanks for this hint