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

     

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