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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

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
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 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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