Forum Discussion
Solved
Split multiple columns into rows
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?
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
2 Replies
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
This is just what I was after, thank you
