Forum Discussion

Elaine Crump's avatar
Elaine Crump
Copper Contributor
May 07, 2018

Transforming rows and columns to a sequence of rows

I have data that looks like this:

  
marya
 b
 c
johna
 b
skipa 
 b
 c
 d
freda
friedaa
 b

 

I want it to look like this:

mary abc 
johnab  
skipabcd
freda   
friedaab  

 

Can it be done quickly and efficiently?  If so, how?

 

Thanks!

 

excel

1 Reply

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    May 07, 2018

    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"