Transforming rows from form to 2 columns with duplicated value

[Is-Jacko]

Hello

Im very new to Power Query so I'm sorry if this is silly.

I have a form that outputs data like this:

Is there an easy way for me to transform that data to look like this?

Thank you

[mussaenda]

Hi @Is-Jacko ,

Select the Outer Num, and Unpivot Columns > Unpivot Other Columns

[mlsx4 · Accepted solution]

Hi @Is-Jacko 

Yes, you only need to unpivot columns on the Power Query Editor. To do so, you select all the columns except "Outer number" and go to Transform > Unpivot

Then you will obtain:

So, you remove the column you don't need: 

And rename column Value:

Here you have the code:

let
    Origen = Excel.Workbook(File.Contents("C:\example.xlsx"), null, true),
    Hoja1_Sheet = Origen{[Item="Hoja3",Kind="Sheet"]}[Data],
    #"Encabezados promovidos" = Table.PromoteHeaders(Hoja1_Sheet, [PromoteAllScalars=true]),
    #"Otras columnas con anulación de dinamización" = Table.UnpivotOtherColumns(#"Encabezados promovidos", {"Outer num"}, "Atributo", "Valor"),
    #"Columnas quitadas" = Table.RemoveColumns(#"Otras columnas con anulación de dinamización",{"Atributo"}),
    #"Columnas con nombre cambiado" = Table.RenameColumns(#"Columnas quitadas",{{"Valor", "Sample"}})
in
    #"Columnas con nombre cambiado"