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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
padinator
Helper I
Helper I

Partially transpose table and replicate column's values recursively for 2 rows

Hello to all! I am completely new to this community and to powerbi and query in general but already found the great benefits you might have by properly using this tool. now that i have started using it i already stumbled over my first big issue which i could not solve by myself. I have the following situation - i have an excel sheet which shows me the inventory and sales of several products in different locations. The problem is that any product repeats itself twice (2 Rows - 1 x Inventory + 1 x Sales) and then the appropriate values per location are displayed on the columns (Loc A | Loc B | Loc C etc.). What i would need to do now, is to replicate these 2 lines (Inventory & Sales) for ANY of these location columns and insert the appropriate values there. So basically i partially need to transpose only the columns of Locations onto the lines => this is what it actually looks like

 

Item NameTypeLoc 1Loc 2Loc 3Loc 4Loc 5 Loc 6Loc 7Loc 8etc
Item 1Inventory1 3 567  
Item 1Sales 2 4     
Item 2Inventory 231 1667062   178
Item 2Sales 12 13156   7
Item 3Inventory2353418249434343115191
Item 3Sales13561211615712
           

 

Now what i would need to do is that fow any item and Type the LOCATION culumns must be "transposed" onto the lines which should look somehow like this

ItemTypeLocationValue
Item1InventoryLoc 11
Item1InventoryLoc 2 
Item1InventoryLoc 33
Item1InventoryLoc 4

 

etc. all Inventory types  

 

Item1SalesLoc 1

 

Item1SalesLoc 2

2

etc. all Sales Type  

 

Item2InventoryLoc 1

231

and so on and so forth  

 

   

 

 

 I guess that i am not the first one having these kind of issue or need but unfortunately i was not able to find anything which could give me a hint of what i could do to solve this in an elegant manner. Any help would be highly appreciated !!

 

Thanks a lot !!

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

Hi @padinator ,

 

In Power Query, multi-select (Ctrl+click) your [Item Name] and [Type] fields.

Then, on the Transform tab, go to Unpivot Columns > Unpivot Other Columns.

 

You can overtype the values highlighted below in the formula bar to change the names of the new columns to something that makes more sense:

BA_Pete_1-1634207119113.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

3 REPLIES 3
BA_Pete
Super User
Super User

Hi @padinator ,

 

In Power Query, multi-select (Ctrl+click) your [Item Name] and [Type] fields.

Then, on the Transform tab, go to Unpivot Columns > Unpivot Other Columns.

 

You can overtype the values highlighted below in the formula bar to change the names of the new columns to something that makes more sense:

BA_Pete_1-1634207119113.png

 

Pete



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

Proud to be a Datanaut!




Genius! Man thats nearly too easy :)) thanks a lot! Really appreaciate your help!

LOL! Welcome to Power Query 👍



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

Proud to be a Datanaut!




Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

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

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.