cancel
Showing results for 
Search instead for 
Did you mean: 
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

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

View solution in original post

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

LOL! Welcome to Power Query 👍

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors