Forum Discussion
Transforming rows and columns to a sequence of rows
I have data that looks like this: mary a b c john a b skip a b c d fred a frieda a b I want it to look like this: ma...
Elaine,
using Power Query:
let
Source = Excel.CurrentWorkbook(){[Name="tbl_Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"XXX", type text}}),
#"Filled Down" = Table.FillDown(#"Changed Type",{"Name"}),
#"Added Index" = Table.AddIndexColumn(#"Filled Down", "Index", 1, 1),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Added Index", {{"Index", type text}}, "de-DE"), List.Distinct(Table.TransformColumnTypes(#"Added Index", {{"Index", type text}}, "de-DE")[Index]), "Index", "XXX"),
#"Merged Columns" = Table.CombineColumns(#"Pivoted Column",{"1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Zusammengeführt"),
#"Trimmed Text" = Table.TransformColumns(#"Merged Columns",{{"Zusammengeführt", Text.Trim, type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Trimmed Text", "Zusammengeführt", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Zusammengeführt.1", "Zusammengeführt.2", "Zusammengeführt.3", "Zusammengeführt.4"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Zusammengeführt.1", type text}, {"Zusammengeführt.2", type text}, {"Zusammengeführt.3", type text}, {"Zusammengeführt.4", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Zusammengeführt.1", "1"}, {"Zusammengeführt.2", "2"}, {"Zusammengeführt.3", "3"}, {"Zusammengeführt.4", "4"}})
in
#"Renamed Columns"