Table Transformation Columns to Rows

[dearears]

Hi All,

I have a table like so...

Material Name	Material Number	Material Type	Transaction Type	Production Order No
A1	123456	FinishedGood	Production	1001
R1	R123	RM	Consumption	1001
R2	R124	RM	Consumption	1001
R3	R125	RM	Consumption	1001
P1	P121	PM	Consumption	1001
P2	P122	PM	Consumption	1001
P3	P123	PM	Consumption	1001
Z1	Z121	RW	Consumption	1001
Z2	Z122	RW	Consumption	1001
A2	245678	SemiFinished	Production	2001
R4	R126	RM	Consumption	2001
R5	R127	RM	Consumption	2001
P4	P125	PM	Consumption	2001
P5	P126	PM	Consumption	2001

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 No	Production Mat Name	Production Mat Number	Production Mat Type	Ingredient Name	Ingredient Number	Ingredient Type
1001	A1	123456	FinishedGood	R1	R123	RM
1001	A1	123456	FinishedGood	R2	R124	RM
1001	A1	123456	FinishedGood	R3	R125	RM
1001	A1	123456	FinishedGood	P1	P121	PM
1001	A1	123456	FinishedGood	P2	P122	PM
1001	A1	123456	FinishedGood	P3	P123	PM
1001	A1	123456	FinishedGood	Z1	Z121	RW
1001	A1	123456	FinishedGood	Z2	Z122	RW
2001	A2	245678	SemiFinished	R4	R126	RM
2001	A2	245678	SemiFinished	R5	R127	RM
2001	A2	245678	SemiFinished	P4	P125	PM
2001	A2	245678	SemiFinished	P5	P126	PM

Can someone guide me on how this can be done?

Thanks in advance.

[SpartaBI · Accepted solution]

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

      

Showcase Report – Contoso By SpartaBI

[dearears]

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

[SpartaBI]

@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 🙂