Forum Discussion
How to split columns and preserve order
Hi,
I'm working with a table where couple of columns have either one or more multiple values in them separated by a pipeline sign .
| ColumnA | ColumnB | ColumnC | ColumnD | ColumnE |
| John Doe | event1 | event 2 | event 3 | date1 | date2 | date3 | eventdetail | eventdetail2 | eventdetail3 | Otherdata |
| Jack Daniels | event1 | date4 | eventdetail4 | Otherdata |
In the example first row above, event1 corresponds to date1 and eventdetail within the same row. How can I break it down preserving this order? :
| ColumnA | ColumnB | ColumnC | ColumnD | ColumnE |
| John Doe | event1 | date1 | eventdetail | Otherdata |
| John Doe | event2 | date2 | eventdetail2 | Otherdata |
| John Doe | event3 | date3 | eventdetail3 | Otherdata |
| Jack Daniels | event1 | date4 | eventdetail4 | Otherdata |
Any help appreciated.
Thank you
Hi patpowerbi
Please try using the following code in Advanced Editor:
(You can download the attached pbix file to view the specific steps.)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFNwyU9V0lFKLUvNKzFUqFEAM4xgDGOgVEpiSSpIBkQbQWljmJaU1JLEzByYcgjPCJULUutfkpFaBNSYqBSrA7Q3MTlbwSUxLzM1pxhuN9QmE1STTVA1xwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ColumnA = _t, ColumnB = _t, ColumnC = _t, ColumnD = _t, ColumnE = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ColumnA", type text}, {"ColumnB", type text}, {"ColumnC", type text}, {"ColumnD", type text}, {"ColumnE", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "ColumnB.1", each Text.Split([ColumnB], " | ")), #"Added Custom1" = Table.AddColumn(#"Added Custom", "ColumnC.1", each Text.Split([ColumnC], " | ")), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "ColumnD.1", each Text.Split([ColumnD], " | ")), #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"ColumnB", "ColumnC", "ColumnD"}), #"Added Custom3" = Table.AddColumn(#"Removed Columns", "Custom", each List.Zip({[ColumnB.1], [ColumnC.1], [ColumnD.1]})), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom3", "Custom"), #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"ColumnB.1", "ColumnC.1", "ColumnD.1"}), #"Extracted Values" = Table.TransformColumns(#"Removed Columns1", {"Custom", each Text.Combine(List.Transform(_, Text.From), " "), type text}), #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", type text}, {"Custom.3", type text}}), #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"ColumnA", "Custom.1", "Custom.2", "Custom.3", "ColumnE"}), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom.1", "ColumnB"}, {"Custom.2", "ColumnC"}, {"Custom.3", "ColumnD"}}) in #"Renamed Columns"Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- bhanu_gautam
Super User
patpowerbi , Use split function in Power query
I split it into rows but that results in multiplication of records ie:
event1 - date1event2 - date1
event3 - date1
event1 - date2
event2 - date2
and so on.
I need to preserve the order of things from those columns withouth mixing them
Hi patpowerbi
Please try using the following code in Advanced Editor:
(You can download the attached pbix file to view the specific steps.)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFNwyU9V0lFKLUvNKzFUqFEAM4xgDGOgVEpiSSpIBkQbQWljmJaU1JLEzByYcgjPCJULUutfkpFaBNSYqBSrA7Q3MTlbwSUxLzM1pxhuN9QmE1STTVA1xwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ColumnA = _t, ColumnB = _t, ColumnC = _t, ColumnD = _t, ColumnE = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ColumnA", type text}, {"ColumnB", type text}, {"ColumnC", type text}, {"ColumnD", type text}, {"ColumnE", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "ColumnB.1", each Text.Split([ColumnB], " | ")), #"Added Custom1" = Table.AddColumn(#"Added Custom", "ColumnC.1", each Text.Split([ColumnC], " | ")), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "ColumnD.1", each Text.Split([ColumnD], " | ")), #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"ColumnB", "ColumnC", "ColumnD"}), #"Added Custom3" = Table.AddColumn(#"Removed Columns", "Custom", each List.Zip({[ColumnB.1], [ColumnC.1], [ColumnD.1]})), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom3", "Custom"), #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"ColumnB.1", "ColumnC.1", "ColumnD.1"}), #"Extracted Values" = Table.TransformColumns(#"Removed Columns1", {"Custom", each Text.Combine(List.Transform(_, Text.From), " "), type text}), #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", type text}, {"Custom.3", type text}}), #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"ColumnA", "Custom.1", "Custom.2", "Custom.3", "ColumnE"}), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom.1", "ColumnB"}, {"Custom.2", "ColumnC"}, {"Custom.3", "ColumnD"}}) in #"Renamed Columns"Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thanks, I got this to work finally !
