Separate Column Values

[MMs]

Hi,

Is it possible to turn this table, in which each milestone has tasks listed just below it in the rows to separate column?

From this:

Project	Milestone/Task
Project 1	Milestone 1
Project 1	Task 1
Project 1	Task 2
Project 1	Task 3
Project 1	Milestone 2
Project 1	Task 1
Project 1	Task 2
Project 1	Milestone 3
Project 1	Milestone 4
Project 1	Task 1
Project 2	Milestone 1
Project 2	Task 1
Project 2	Task 2
…	…

To something like this:

Project	Milestone	Task
Project 1	Milestone 1	Task 1
Project 1	Milestone 1	Task 2
Project 1	Milestone 1	Task 3
Project 1	Milestone 2	Task 1
Project 1	Milestone 2	Task 2
Project 1	Milestone 4	Task 1
Project 2	Milestone 1	Task 1
Project 2	Milestone 1	Task 2
…	…	…

[alannavarro · Accepted solution]

Hello, hope it helps.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlEwVNJR8s3MSS0uyc9LBfJidVDlQhKLs3EJG2EXNsYQRliAQwt2CzAVIwzCZ4kJipwRqg8VMCSxuMAIyYuxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, #"Milestone/Task" = _t]),
Custom1 = Table.AddColumn(Source,"Custom", each if Text.Contains([#"Milestone/Task"], "Milestone") then [#"Milestone/Task"] else null),
#"Filled Down" = Table.FillDown(Custom1,{"Custom"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each not Text.Contains([#"Milestone/Task"], "Milestone")),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Custom", "Milestone"}, {"Milestone/Task", "Task"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Project", "Milestone", "Task"})
in
#"Reordered Columns"

[SidTrengove]

Absolutely!

You can add two custom columns: one for milestones and one for tasks.

You can then fill down the results of these columns to get your answer

I have written the M code for you if you want to copy and paste it into your advanced editor.

Thanks!   

Code: 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlEwVNJR8s3MSS0uyc9LBfJidVDlQhKLs3EJG2EXNsYQRliAQwt2CzAVIwzCZ4kJipwRqg8VMCSxuMAIyYuxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, #"Milestone/Task" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Milestone/Task", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Milestone", each if Text.Contains( [#"Milestone/Task"], "Milestone" ) then [#"Milestone/Task"] else null),
    Custom1 = Table.AddColumn(#"Added Custom", "Task", each if Text.Contains( [#"Milestone/Task"], "Task" ) then [#"Milestone/Task"] else null),
    #"Filled Down1" = Table.FillDown(Custom1,{"Task"}),
    #"Filled Down" = Table.FillDown(#"Filled Down1",{"Milestone"}),
    #"Replaced Value" = Table.ReplaceValue(#"Filled Down",null,"Task1",Replacer.ReplaceValue,{"Task"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"Milestone/Task"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Milestone", type text}, {"Task", type text}})
in
    #"Changed Type1"

---

Appreciate your Kudos!

Please accept the solution if it answers your question 🙂

Easy Power BI Tutorials

Unleash the full potential of Power BI with Help Xel

Visit HelpXel