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
You can also do this using PowerQuery.The steps are as follows:
Unpivot Other Columns
Remove
Split Columns
=> By Delimeter
=> OK
Close & Load
This is the result in PowerQuery (just before step 7)
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"