Please help -should I Pivot or Split by Delimiter?

The answer is -> both.

The pattern for these things is :

Split the columns

Select the key column (the first here) and Unpivot other columns.

You will get an Attribute column which holds information that we can use (1,2,3 or column2 column3)

Split the Attribute column (on ".")

You then Pivot the Attribute.1 column. Use Value in Values and choose Advanced-> Don't Aggregate.

 

Let me know how you get on

 

Here is another way to do in the query editor in mostly one step, making a custom column that makes a List of Records from the columns.  I did an example with letters and numbers, but note that both input column need to be text format (you can convert to number after, if needed).

To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Hci3DcAwEATBXi7ehKRsKEOZGh7ffxs6KBrsRqgIbewctlBpSkLVddK57MDI9N/munl47czCqswP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Letters = _t, Numbers = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Letters", type text}, {"Numbers", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Transform(List.Zip({Text.Split([Letters],","), Text.Split([Numbers],",")}), each Record.FromList(_, {"Letter", "Number"}))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Letters", "Numbers"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
    #"Expanded Custom1" = Table.ExpandRecordColumn(#"Expanded Custom", "Custom", {"Letter", "Number"}, {"Letter", "Number"})
in
    #"Expanded Custom1"

 

Regards,

Pat

Did you try the approach in my suggestion?  It should even with mutliple columns with comma delimited lists in them.  You would just add a third Text.Split inside the List.Zip part, and a third column name inside the Record.FromList part.

Regards,

Pat