Forum Discussion

padinator's avatar
padinator
Helper I
4 years ago
Solved

Dynamically rearrange rows into one Header column in Power Query

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 AHeader BHeader C....Header J    
    2020-W012020-W022020-W032020-W04
Text AText BText C etcText J1234432
        

 

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 AHeader BHeader CHeader J2020-W12020-W22020-W32020-W4
Text AText BText CText J1234432
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!!!

  • 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

2 Replies

  • 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

  • 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