excelvbaexcel-formulapowerqueryunpivot

Is there a way to transpose groups of columns to rows in excel?

My source table in excel looks like this:

code name1 perc1 name2 perc2 name3 perc3
11 x 10 x2 20 x3 70
12 y 20 y2 80
13 z 100
45 q 15 q2 85

and here is the final table I need:

code    name1   perc1
11      x        10
11      x2       20
11      x3       70
12      y        20
12      y2       80 
13      z       100
45      q        15
45      q2       85
Solution

  • You can also do this using PowerQuery.The steps are as follows:

    1. Merge columns name1 & perc1, name2 & perc2 , name3 & perc3, using a delimeter (say equalsign). Now your left with 4 columns.
    2. Right-click column [code] and select Unpivot Other Columns
    3. Right-click column [Attribute] and select Remove
    4. Right-click column [Value] and select Split Columns => By Delimeter=> OK
    5. Using the dropdown-menu in Column [Value.2]: deselect the null values.
    6. Rename column [Value.1] and [Value.2]
    7. Click Close & Load

    This is the result in PowerQuery (just before step 7)

    enter image description here

    And this is the generated script from the Advanced Editor:

    let
    Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"code", Int64.Type}, {"name1", type text}, {"perc1", Int64.Type}, {"name2", type text}, {"perc2", Int64.Type}, {"name3", type text}, {"perc3", Int64.Type}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"perc1", type text}}, "en-US"),{"name1", "perc1"},Combiner.CombineTextByDelimiter("=", QuoteStyle.None),"Merged"),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"perc2", type text}}, "en-US"),{"name2", "perc2"},Combiner.CombineTextByDelimiter("=", QuoteStyle.None),"Merged.1"),
    #"Merged Columns2" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns1", {{"perc3", type text}}, "en-US"),{"name3", "perc3"},Combiner.CombineTextByDelimiter("=", QuoteStyle.None),"Merged.2"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Merged Columns2", {"code"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Value", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Value.1", "Value.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", type text}, {"Value.2", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Value.2] <> null)),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Value.1", "Name1"}, {"Value.2", "Perc1"}})
in
    #"Renamed Columns"