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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors