Forum Discussion
Transforming Data Table
Have a sheet of data as below Col1/ Col2...NN
Col1 (Txt) Col 2..NN
---------- -- -- -- -- --
abc c1:v1 5 23 102
xyz cx:vz 23 101 5
vav cN:VU 103 23 5
what is the easy way to transform into the below?
col1 Col2 Col3 Col4
5 abc c1:v1 xyz cx:vz vav cN:VU
23 abc c1:v1 xyz cx:vz vav cN:VU
101 xyz cx:vz
102 abc c1:v1
103 vav cN:VU
Li Chen,
if you can use Power Query then this would be the (not optimized) code.
let
Source = Excel.CurrentWorkbook(){[Name="tbl_Data"]}[Content],
#"Replaced Value" = Table.ReplaceValue(Source," ","#",Replacer.ReplaceText,{"Txt"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Txt", type text}, {"NN", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "NN", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"NN.1", "NN.2", "NN.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"NN.1", Int64.Type}, {"NN.2", Int64.Type}, {"NN.3", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Txt", "Index"}, "Attribut", "Wert"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribut"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Index", type text}}, "de-DE"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Index", type text}}, "de-DE")[Index]), "Index", "Txt"),
#"Merged Columns" = Table.CombineColumns(#"Pivoted Column",{"1", "2", "3"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Zusammengeführt"),
#"Trimmed Text" = Table.TransformColumns(#"Merged Columns",{{"Zusammengeführt", Text.Trim, type text}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Trimmed Text", "Zusammengeführt", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Zusammengeführt.1", "Zusammengeführt.2", "Zusammengeführt.3"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Zusammengeführt.1", type text}, {"Zusammengeführt.2", type text}, {"Zusammengeführt.3", type text}}),
#"Replaced Value1" = Table.ReplaceValue(#"Changed Type2","#"," ",Replacer.ReplaceText,{"Zusammengeführt.1", "Zusammengeführt.2", "Zusammengeführt.3"}),
#"Renamed Columns" = Table.RenameColumns(#"Replaced Value1",{{"Zusammengeführt.1", "1"}, {"Zusammengeführt.2", "2"}, {"Zusammengeführt.3", "3"}, {"Wert", "NN"}})
in
#"Renamed Columns"
