Forum Discussion

Copper Contributor

Jul 28, 2020

Solved

Convert 912 rows and 16 columns into 2 columns....with a twist

Hello All, I've got a spreadsheet with 912 rows and 16 columns, each is row contains a product model # and the 1 to 15 colours that are available for each model. I need to convert this to a verti...

excel

SergeiBaklan
Jul 29, 2020
kcb410

You may open the file, Data->Query and Connections, on the right pane double click on query (or Edit from right click menu), query editor will be opened, here you will see the steps

SergeiBaklan

Diamond Contributor

Jul 28, 2020

kcb410

Power Query works - name the range, query it, select first column, Unpivot other columns, remove Attribute column, return result back to Excel sheet.

kcb410
Copper Contributor
Jul 28, 2020
SergeiBaklan

Thanks for the reply.

I've tried using unpivot other but I've been unable to make it format the data the way I need it. For instance if there's 2 colours I need the model number repeated twice, if there's 7 colours I need the model number repeated 7 times. I know Power Query will achieve this, I just don't know how.
- SergeiBaklan
  Diamond Contributor
  Jul 28, 2020
  kcb410
  
  Please check in attached file, that's just two steps query
  
  unpivot colors.xlsx24 KB
  - kcb410
    Copper Contributor
    Jul 29, 2020
    SergeiBaklan
    Hi Sergei,
    
    Thanks for sending that file.
    Excuse my ignorance on this topic, I've never used Power Query. I assume the first step is Unpivot other columns. What's the final step/query?