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
dearears
Helper I
Helper I

Table Transformation Columns to Rows

Hi All,

 

I have a table like so...

 

Material NameMaterial NumberMaterial TypeTransaction TypeProduction Order No
A1123456FinishedGoodProduction1001
R1R123RMConsumption1001
R2R124RMConsumption1001
R3R125RMConsumption1001
P1P121PMConsumption1001
P2P122PMConsumption1001
P3P123PMConsumption1001
Z1Z121RWConsumption1001
Z2Z122RWConsumption1001
A2245678SemiFinishedProduction2001
R4R126RMConsumption2001
R5R127RMConsumption2001
P4P125PMConsumption2001
P5P126PMConsumption2001

 

The table summarises by order number: goods produced (marked as Production under transaction type) and the ingredients consumed to product the good (marked as Consumption under transaction type). 

 

From this table I want to create another table or transfor this table to show the data as

 

Production Order NoProduction Mat NameProduction Mat NumberProduction Mat TypeIngredient NameIngredient NumberIngredient Type
1001A1123456FinishedGoodR1R123RM
1001A1123456FinishedGoodR2R124RM
1001A1123456FinishedGoodR3R125RM
1001A1123456FinishedGoodP1P121PM
1001A1123456FinishedGoodP2P122PM
1001A1123456FinishedGoodP3P123PM
1001A1123456FinishedGoodZ1Z121RW
1001A1123456FinishedGoodZ2Z122RW
2001A2245678SemiFinishedR4R126RM
2001A2245678SemiFinishedR5R127RM
2001A2245678SemiFinishedP4P125PM
2001A2245678SemiFinishedP5P126PM

 

Can someone guide me on how this can be done?

 

Thanks in advance.

1 ACCEPTED SOLUTION
SpartaBI
Community Champion
Community Champion

@dearears I did it with Power Query.
Link to download the file with the solution: Table Transformation Columns to Rows.pbix


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

View solution in original post

3 REPLIES 3
SpartaBI
Community Champion
Community Champion

@dearears I did it with Power Query.
Link to download the file with the solution: Table Transformation Columns to Rows.pbix


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

Thank you ! Works like a charm and your logic is very clear and straight forward.

@dearears my pleasure 🙂
Hey, check out my showcase report - got some high level stuff there 🙂
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up over there if you liked it 🙂

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.