Forum Discussion

MickThurman's avatar
MickThurman
Frequent Visitor
5 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
fhill
Icon for Resident Rockstar rankResident Rockstar
5 years ago

 

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?