Forum Discussion

TCatron18's avatar
TCatron18
Copper Contributor
Jun 03, 2026
Solved

Power Query Editor - Unpivot Multiple Delimited Columns

Hello-

I'm looking for the most efficient way to unpivot multiple columns that have delimited data.

For example, I have the following data:

DateUserProductPositionJob
1/1/20261234ABCPosition 1 | Position 2Job 1 | Job 2
1/1/20265678DEFPosition 3Job 3

And I'm looking for the data to be as follows:

DateUserProductPositionJob
1/1/20261234ABCPosition 1Job 1
1/1/20261234ABCPosition 2Job 2
1/1/20265678DEFPosition 3Job 3

 

excel
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Jun 04, 2026

    TCatron18​ 

    Perhaps not the most elegant way but the attached file contains a solution that works with your specific example, with the following M-code:

    let Source = Excel.CurrentWorkbook(){[Name= "Table1"]}[Content], Type = Table.TransformColumnTypes(Source,{{"Date", type date}}), SplitPosition = Table.ExpandListColumn(Table.TransformColumns(Type, {{"Position", Splitter.SplitTextByDelimiter(" | ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Position"), RemoveJob = Table.RemoveColumns(SplitPosition,{"Job"}), SplitJob = Table.ExpandListColumn(Table.TransformColumns(Type, {{"Job", Splitter.SplitTextByDelimiter(" | ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Job"), RemovePos = Table.RemoveColumns(SplitJob,{"Position"}), AddIndex = Table.AddIndexColumn(RemovePos, "Index", 0, 1, Int64.Type), AddPos = Table.AddColumn(AddIndex, "Position", each RemoveJob[Position]{[Index]}), Remove = Table.RemoveColumns(AddPos,{"Index"}), Reorder = Table.ReorderColumns(Remove,{"Date", "User", "Product", "Position", "Job"}) in Reorder

     

    The system doesn't let me attach the file, unfortunately.

2 Replies

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Jun 04, 2026

    TCatron18​ 

    Perhaps not the most elegant way but the attached file contains a solution that works with your specific example, with the following M-code:

    let Source = Excel.CurrentWorkbook(){[Name= "Table1"]}[Content], Type = Table.TransformColumnTypes(Source,{{"Date", type date}}), SplitPosition = Table.ExpandListColumn(Table.TransformColumns(Type, {{"Position", Splitter.SplitTextByDelimiter(" | ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Position"), RemoveJob = Table.RemoveColumns(SplitPosition,{"Job"}), SplitJob = Table.ExpandListColumn(Table.TransformColumns(Type, {{"Job", Splitter.SplitTextByDelimiter(" | ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Job"), RemovePos = Table.RemoveColumns(SplitJob,{"Position"}), AddIndex = Table.AddIndexColumn(RemovePos, "Index", 0, 1, Int64.Type), AddPos = Table.AddColumn(AddIndex, "Position", each RemoveJob[Position]{[Index]}), Remove = Table.RemoveColumns(AddPos,{"Index"}), Reorder = Table.ReorderColumns(Remove,{"Date", "User", "Product", "Position", "Job"}) in Reorder

     

    The system doesn't let me attach the file, unfortunately.