Data Shaping - Single Column into Multiple Columns

[Anonymous]

Fellow Community,

I would like to do the following transformation with a single column data set.

In the example below I would like to use the character "x" as a  indicator to start a new column.

When complete I would have a table of multipule columns

Any help is appreciated!!

Starting Column

Vertical Column

After transformation I would like to have this

Horizontal Table

[parry2k]

@Anonymous would this work, column header has to be unique, cannot all be X

[Anonymous]

I think this will work, can you share your steps?

[parry2k · Accepted solution]

@Anonymous this is just a rough idea , can be improved but I guess this will get you started.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WilCK1YlWMgSTRmDSGEyagElTMIlLDTF6zQipjAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "X Col", each if [Column1]="X" then [Column1] & Number.ToText([Index]) else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"X Col"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column1] <> "X")),
    #"Added Custom2" = Table.AddColumn(#"Filtered Rows", "Custom", each [Column1]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Index"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"X Col"]), "X Col", "Column1"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Custom"})
in
    #"Removed Columns1"