Forum Discussion

MattAtBP's avatar
MattAtBP
Frequent Visitor
1 year ago
Solved

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's avatar
    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's avatar
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's avatar
Omid_Motamedise
Super User
1 year ago

it will result in