Split multiple columns and 'join/assign'

[MattAtBP]

Hi,

Recently got a data set that has multiple columns, each contain mulitiple values separated by pipe. I'm trying to separate them out, but then assign as shown below. The first instance / value of column one, matches to the first instance / value in second column, 2nd matches to second etc etc. Apologies if I'm not explaining it very well - i'm hoping the picture below does a better job.

[MattAtBP]

Thank you all @slorin @Omid_Motamedise @ThxAlot  for your responses - all three appoaches worked well.

[Omid_Motamedise]

Hi @MattAtBP  

This is solution for your question, just copy it and past it into the advance editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMq4xrbFU0lFyrHGqcVaK1YlWMqoxAfJdalzBPMMasxrzGgugiFuNe41HjadSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Risk ID" = _t, #"Risk Title" = _t]),
    split = Table.TransformColumns(Source,{{"Risk ID", each Text.Split(_,"|")}, {"Risk Title",each Text.Split(_,"|")}}),
    merged = Table.CombineColumns(split,{"Risk ID", "Risk Title"},List.Zip,"Merged"),
    #"Expanded Merged" = Table.ExpandListColumn(merged, "Merged"),
    Custom1 = Table.SplitColumn(#"Expanded Merged","Merged", each _,{"ID","Title"}),
    #"Sorted Rows" = Table.Sort(Custom1,{{"ID", Order.Ascending}})
in
    #"Sorted Rows"

If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos. 

Thank you!

[Omid_Motamedise]

it will result in 

[Omid_Motamedise]

Its going to convert 

to this one

[ThxAlot]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RYy7DsIwDEV/xcrMwhvmIjGBQOpWdTCpC1bTGCUOCCkfT4CB8R6dc5vGzPMyb83EnEQC7FARzgkd6wsy7EmV/RVUBMR1hRxFgb1SoKjUmXbSmFlelLyS8Z4Kh0sgHOJHJX1KGEAeFJzgT57mVV7nTQlqsjfPFl3Hfc82OWUqWSVRv0ny5aPmkf7rgDYI9MgumrZ9Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Risk ID" = _t, #"Risk Title" = _t]),
    #"Transformed rows" = List.Transform(Table.ToRows(Source), (row) => Table.FromColumns(List.Transform(row, each List.Transform(Text.Split(_, "|"), Text.Trim)), {"Risk ID","Risk Title"})),
    Combined = Table.Combine(#"Transformed rows")
in
    Combined

[slorin · Accepted solution]

Hi,  @MattAtBP 

= Table.FromColumns(
  {
    List.Combine(List.Transform(YourSource[Risk ID], each Text.Split(_, " | "))), 
    List.Combine(List.Transform(YourSource[Risk Title], each Text.Split(_, " | ")))
  }, 
  {"Risk ID", "Risk Title"}
)

then sort by ID

Stéphane