Split column by delimiter and also relate them in the same order

[escobardavila]

Hi everybody!

I really appreciate your help to see how I can solve this. I have an excel file with this data:

At the end I have to represent the data like this: for each line each serial

How can I do it?

I did a test: unpivot, but it's not what I need 😞

And another test with PO Line and Serial column split: but I can't get what I want to show either.

I appreciate your ideas and support.

Regards,

Lina

[lbendlin · Accepted solution]

This can be optimized, but gives you the general idea.  Note the inconsistency - PO Line values are only separated by comma, Serial values also have spaces.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VYzBCsMwDEN/Zfjsge3EsXceYZdtDaUwRsn//8bs0UsFEpIOb9+hiN1sHf26fZ6AsK1SR78vlGoeT38tkVW9kCsLx0j39yASmJgIFz0jHt8DwXxGmJCWRKBgwYqKLZaZ4sUaR5hnq9n+n8KcPw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"DO Final" = _t, #"Reference / Subject e-mail" = _t, Priority = _t, #"SAP PO" = _t, #"PO Line" = _t, Serial = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Zip({Text.Split([PO Line],","),Text.Split([Serial],",")})),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each Record.FromList([Custom],type [#"PO Line"=text, Serial=text])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"DO Final", "Reference / Subject e-mail", "Priority", "SAP PO", "Custom.1"}),
    #"Expanded Custom.1" = Table.ExpandRecordColumn(#"Removed Other Columns", "Custom.1", {"PO Line", "Serial"}, {"PO Line", "Serial"})
in
    #"Expanded Custom.1"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

[escobardavila]

@lbendlin Thank you so much! It worked 😊