Split multiple columns and 'join/assign'

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...

Need Help

slorin

1 year ago

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

Omid_Motamedise

Super User

1 year ago

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

Super User

1 year ago

Its going to convert