creating multiple rows of data based on values in columns

[mzza]

Hi all,


I have a spreadsheet which currently looks something like this:

ID | Code1 | Code2 | Code3 | etc...
AA | 11122 | 31243 | 54645 | 
BB | 12398 | 23242 |       |
CC | 76766 |       |       |

However, I need it to look as follows:

ID | Code
AA | 11122
AA | 31243
AA | 54645
BB | 12398
BB | 23242
CC | 76766

... and I'm stumped on how to achieve this. Can anyone help? There are 18 'Code x' columns and 109,000 rows of data fwiw.

 

[shift-del]

Hi

Load the table into Power Query.
Select column "ID".
Right click. "Unpivot other columns".
Close and load the new table into Excel.

 

[sandy666]

if you do it correctly (post#2), you should get the result:

ID	Code1	Code2	Code3		ID	Code
AA	11122​	31243​	54645​		AA	11122​
BB	12398​	23242​			AA	31243​
CC	76766​				AA	54645​
					BB	12398​
					BB	23242​
					CC	76766​

update

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UnPivot = Table.UnpivotOtherColumns(Source, {"ID"}, "Attribute", "Value"),
    RC = Table.RemoveColumns(UnPivot,{"Attribute"}),
    Ren = Table.RenameColumns(RC,{{"Value", "Code"}})
in
    Ren[/SIZE]

 

[mzza]

Beautiful, thank you so much!

 

[sandy666]

You are welcome