Forum Discussion
Solved
Split one column with multiple rows into multiple colunms and rows
I have one column of data with multiple rows, shown below. What I need to do is create 6 columns and split the data out. See second image.
What I have today
What I need
Hi rpalardy,
Result:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8g1RitUBUpk5+UU6CiEZmYl56WCRklyQEJjpWlEAZKZCODmJeiV5JXrJ+blgrpejn4KhsY6CkYERkDQ0tDI1U3D0BUuBCXdniBGZGSWl6ToKzkWVxSWZqWCx5FSwIKYVeVisMIJZYWxlYqoQgGRDsCuYckxJzM0sztBRcC3KTE6EGJoIEcO0IbEAwwZTuB+sTC3BXogFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), FilteredRows = Table.SelectRows(Source, each ([Column1] <> "")), Transformed = Table.FromRows(List.Split(List.Transform(Table.ToList(FilteredRows), (x)=> Text.Remove(x, """")),6), {"Initials", "Name", "email", "user type", "appendix", "date"}) in Transformed
1 Reply
Hi rpalardy,
Result:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8g1RitUBUpk5+UU6CiEZmYl56WCRklyQEJjpWlEAZKZCODmJeiV5JXrJ+blgrpejn4KhsY6CkYERkDQ0tDI1U3D0BUuBCXdniBGZGSWl6ToKzkWVxSWZqWCx5FSwIKYVeVisMIJZYWxlYqoQgGRDsCuYckxJzM0sztBRcC3KTE6EGJoIEcO0IbEAwwZTuB+sTC3BXogFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), FilteredRows = Table.SelectRows(Source, each ([Column1] <> "")), Transformed = Table.FromRows(List.Split(List.Transform(Table.ToList(FilteredRows), (x)=> Text.Remove(x, """")),6), {"Initials", "Name", "email", "user type", "appendix", "date"}) in Transformed