Forum Discussion

Is-Jacko's avatar
Is-Jacko
New Member
3 years ago
Solved

Transforming rows from form to 2 columns with duplicated value

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? ...
Need Help
  • mlsx4's avatar
    mlsx4
    3 years ago

    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"

     

mlsx4's avatar
mlsx4
Icon for Memorable Member rankMemorable Member
3 years ago

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"