Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Data Shaping - Single Column into Multiple Columns

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

Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    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"

     

3 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

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

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

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

       
       
       
       
       
       
       
       
       
      • parry2k's avatar
        parry2k
        Super User
        6 years ago

        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"