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. W...
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"

     

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"