How to split columns and preserve order

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.