Associating Piped Values in Different Columns

[leibowjb]

Below I have a dataset where in the left column, "Operation Sale Types" I have various sales types that are contained within pipes. If you look at line 9, you can see that there are 2 different operation sale types. In the right column, "Operation Line Cost", is the associated value for each type also contained within pipes. I want to be able to associate value 1 within column A to value 1 within column B, and so on. Any recommendations?

[v-shex-msft · Accepted solution]

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

[Greg_Deckler]

Seems like you are going to have to do a split on those columns based on your pipe character and then do some sort of pivot or something. Can you post your sample data as something that can be copied easily?