Transposing data

[MickThurman]

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 

[v-rzhou-msft]

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

[v-rzhou-msft · Accepted solution]

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. 

[fhill]

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?

[vanessafvg]

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?

[MickThurman]

Hi, Yes one row, for these data. Will be using the data against a raft other attributes from other tables to manage their consolidation.

[Greg_Deckler]

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