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

 

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

     

6 Replies

  • slorin's avatar
    slorin
    Super User
    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

     

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

  • 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!