Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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.
Solved! Go to Solution.
@dearears I did it with Power Query.
Link to download the file with the solution: Table Transformation Columns to Rows.pbix
@dearears I did it with Power Query.
Link to download the file with the solution: Table Transformation Columns to Rows.pbix
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 🙂
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
77 | |
76 | |
57 | |
36 | |
34 |
User | Count |
---|---|
99 | |
56 | |
56 | |
46 | |
40 |