cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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:

 

ProjectProject LocationStep NumberStep DetailStep Owner
Project ATownsville1, 2, 3, 4"Make a plan", "Check the plan", "Do the plan", "Review the plan""Bob", "Greg", "Dave", "Bob"
Project BCityberg1, 2, 3"Construct building", "Use building", "Deconstruct building""Anna", "Jane", "Steph"
Project CVillagetown1, 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:

ProjectProject LocationStep NumberStep DetailStep Owner
Project ATownsville1"Make a plan""Bob"
Project ATownsville2"Check the plan""Greg"
Project ATownsville3"Do the plan""Dave"
Project ATownsville4"Review the plan""Bob"
Project BCityberg1"Construct building""Anna"
Project BCityberg2"Use building""Jane"
Project BCityberg3"Deconstruct building""Steph"
Project CVillagetown1"Write a book""Sam"
Project CVillagetown2"Publish a book""Chris"
Project CVillagetown3"Sell the book""Taylor"

 

using Power Query or DAX. Is this possible?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User VI
Super User VI

Re: Split multiple columns into rows

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





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
Highlighted
Super User VI
Super User VI

Re: Split multiple columns into rows

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





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




View solution in original post

Highlighted
Frequent Visitor

Re: Split multiple columns into rows

This is just what I was after, thank you

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors