Data help and tips

Hi Anonymous 

You can try the following solution. It's a combination of M and DAX.

First main step in Power Query editor is to select Project ID column and unpivot other columns. Please note that change all phase date columns' data type into Text before unpivoting. If they are of date type, the null values will be removed after unpivoting.

The other main step is to add Phase ID column by adding conditional column. 

Below are all steps of transformation I have implemented. You can paste the codes into a blank query's Advanced editor to check the details.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY7BDcAgDAN3yRskxy2UzILYf40CVUuoxOdkn0mtQqodEkRLhEaC2oHw8E/4Ao7Y34QW+pQ9U5utXtDkwT47yBwoV7I8BrYaXG0ky1naaeMcuhC+iexAuf3bbg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project ID" = _t, #"Start Phase" = _t, #"Planning phase" = _t, #"Execution phase" = _t, #"Monitor phase" = _t, Live = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project ID", Int64.Type}, {"Start Phase", type text}, {"Planning phase", type text}, {"Execution phase", type text}, {"Monitor phase", type text}, {"Live", type text}}, "en-GB"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project ID"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Attribute.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute.1", "Phase"}, {"Value", "Start"}}),
    #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "Phase ID", each if [Phase] = "Start" then 1 else if [Phase] = "Planning" then 2 else if [Phase] = "Execution" then 3 else if [Phase] = "Monitor" then 4 else if [Phase] = "Live" then 5 else null),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Conditional Column",{"Phase ID", "Project ID", "Phase", "Start"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Reordered Columns",{{"Phase ID", Int64.Type}, {"Start", type date}}, "EN-GB")
in
    #"Changed Type2"

After applying the query into Power BI Desktop, you can use below DAX codes to add a calculated column.

End = MINX(FILTER('Table','Table'[Project ID]=EARLIER('Table'[Project ID])&&'Table'[Phase ID]=EARLIER('Table'[Phase ID])+1),'Table'[Start])

Result

Download the attachment to check the details. Hope this helps.

Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

Hi,

You may download my PBI file from here.

Hope this helps.