Associating Piped Values in Different Columns

Hi leibowjb,

You can refer to below steps to expand those mixed columns.

Steps:

1. Enter to query editor.

2. Add custom column to transfer these column to table.

#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.FromColumns({Text.Split([Type],"|"),Text.Split([Cost],"|")},{"Type","Cost"}))

3. Remove origianl type, cost columns.

#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"ID", "Custom"})

4. Expand table to new row.

Full query:

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Desktop\sample.xlsx"), null, true),
    #"Merge Records_Sheet" = Source{[Item="Merge Records",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Merge Records_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Type", type text}, {"Cost", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.FromColumns({Text.Split([Type],"|"),Text.Split([Cost],"|")},{"Type","Cost"})),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"ID", "Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Type", "Cost"}, {"Type", "Cost"})
in
    #"Expanded Custom"

Regards,

Xiaoxin Sheng