Forum Discussion
Transposing data
Hi, I have a table which is a long list of materials, some of which are duplicates - in that they are the same material but with a different number. Fortunately, as can be seen in the picuture, these materials can identified through cross referencing the details in the 'Old material number' and 'Legacy Material Number' columns. To aid some downstram modelling we need to do, ideally what I would like to do is stack these two records up side by side - possibly in another table? - with the 'SC???????' material (and its associated data elements) to the right of the ?????/???? material.
Any thoughts on how to do this would be much appreciated?
Mick
Hi MickThurman
You can try merge function in power query editor.
Firstly we need to duplicate this table in power query editor.
Keep first top row in Table(2)
Then merge Table (2) and Table by Old material number column in Table(2) and Legacy Material Number column in Table.
Final expand all rows in Table.
Result is as below.
All elements will show in one row.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Hi MickThurman
You can try merge function in power query editor.
Firstly we need to duplicate this table in power query editor.
Keep first top row in Table(2)
Then merge Table (2) and Table by Old material number column in Table(2) and Legacy Material Number column in Table.
Final expand all rows in Table.
Result is as below.
All elements will show in one row.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
MickThurman - Not entirely sure what you want as output. Can you provide an example. There is a transpose function in Power Query and I wrote a DAX solution for transpose as well. TRANSPOSE (powerbi.com)
so you want one row, but to keep all the legacy attributes but in one row, is that what you are saying? how will you be using this data?
Hi, Yes one row, for these data. Will be using the data against a raft other attributes from other tables to manage their consolidation.
Speaking just in theory, as we'll need more sample data to really help step by step. Here's one idea....
* Copy this table to a new table
* Filter rows to remove any row with a Legacy Value
* Perform a Lookup in a new column, matching Old Material Number to the (Original Table) Legacy Material Column
* Return from the Lookup whatever values you need?
Hi MickThurman
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.
Best Regards,
Rico Zhou
