Forum Discussion
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...
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"
I think this will work, can you share your steps?
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"