Split multiple columns into rows

[Tom_Morris]

Hi there,

I have some data which is structured like the below table, where columns 3, 4 and 5 contain lists of values separated by a delimiter:

Project	Project Location	Step Number	Step Detail	Step Owner
Project A	Townsville	1, 2, 3, 4	"Make a plan", "Check the plan", "Do the plan", "Review the plan"	"Bob", "Greg", "Dave", "Bob"
Project B	Cityberg	1, 2, 3	"Construct building", "Use building", "Deconstruct building"	"Anna", "Jane", "Steph"
Project C	Villagetown	1, 2, 3	"Write a book", "Publish a book", "Sell the book"	"Sam", "Chris", "Taylor"

I would like to split these columns into the number of rows as there are elements in these lists, resulting in something that looks like this:

Project	Project Location	Step Number	Step Detail	Step Owner
Project A	Townsville	1	"Make a plan"	"Bob"
Project A	Townsville	2	"Check the plan"	"Greg"
Project A	Townsville	3	"Do the plan"	"Dave"
Project A	Townsville	4	"Review the plan"	"Bob"
Project B	Cityberg	1	"Construct building"	"Anna"
Project B	Cityberg	2	"Use building"	"Jane"
Project B	Cityberg	3	"Deconstruct building"	"Steph"
Project C	Villagetown	1	"Write a book"	"Sam"
Project C	Villagetown	2	"Publish a book"	"Chris"
Project C	Villagetown	3	"Sell the book"	"Taylor"

using Power Query or DAX. Is this possible?

[mahoneypat · Accepted solution]

Here is an example of how to do this in the query editor.  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("bZDNCsIwEIRfJeSciz8v0FYQBEGsPwfrYVOXNjYmkqQtvr0xqajF2+7s7PAxpxPdGH3F0pGEMrrTvbKdkBL9MmFkysiMkblfCrqGBgmQuwRVUEYKmtVYNsTV+KUt9EjYYiew/xJDVqp5PC8NVsMndBincKRn9kFL/VMm3IOjqT5gISnTyjrTehNvhbwINcTtLY6UBZZ/vCEkUQqiaQVqoMgd3usRR+bdB98OVOh8UyOUoxHu1RDXuokZm5ZLYesfLUcpQx1RCp853N6VGmHjuIOH1OYFcH4C", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, #"Project Location" = _t, #"Step Number" = _t, #"Step Detail" = _t, #"Step Owner" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Project Location", type text}, {"Step Number", type text}, {"Step Detail", type text}, {"Step Owner", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Zip({Text.Split([Step Detail], ","), Text.Split([Step Owner], ",")})),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Step Detail", "Step Owner"}),
    #"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Custom", each Text.Combine(List.Transform(_, Text.From), "#(tab)"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByEachDelimiter({"#(tab)"}, QuoteStyle.Csv, false), {"Custom.1", "Custom.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Custom.1", "Step Detail"}, {"Custom.2", "Step Owner"}}),
    #"Trimmed Text" = Table.TransformColumns(#"Renamed Columns",{{"Step Detail", Text.Trim, type text}, {"Step Owner", Text.Trim, type text}})
in
    #"Trimmed Text"

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Tom_Morris]

This is just what I was after, thank you