Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
… | … | … |
Solved! Go to 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"
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"
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 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.