Not able to pivot columns in case there is a delimiter in the column values

[rohit1410]

Hi all,
I have the data in the below format - 

And I want to convert this data in the below format - 

Can someone help with the steps ?

I tried using the 'Split Column' and 'Pivot Column' but not able to get it right.

Thanks in advance

[CNENFRNL · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUUpOLE7VKUjMyUkt0SlPzUzPKAEKGhroGBnoGBoYKMXqRCs5QZUBKSNTsIgzkAlRDNUK1gPSBNQRCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    Columns = Table.ColumnNames(Source),
    #"Separated text" = Table.TransformColumns(Source, {{Columns{1}, each Text.Split(_, ",")}, {Columns{2}, each Text.Split(_, ",")}}),
    #"Combined columns" = Table.CombineColumns(#"Separated text", List.Skip(Columns), each Record.FromList(_{1}, _{0}), "Combined"),
    #"Expanded Combined" = Table.ExpandRecordColumn(#"Combined columns", "Combined", {"case", "pallet", "weight"})
in
    #"Expanded Combined"