Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
padinator
Helper I
Helper I

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!!!

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

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.

 

BA_Pete_0-1635937104078.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

2 REPLIES 2
padinator
Helper I
Helper I

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

BA_Pete
Super User
Super User

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.

 

BA_Pete_0-1635937104078.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors