Data help and tips

[Anonymous]

Hi all,

I tried to make a calender visual, like here in the forum that I posted. Unfortunatelly I don't see an other option than Restructuring my data. 

I have the following data 

Project ID	Start Phase	Planning phase	Execution phase	Monitor phase	Live
22193	18-01-2021	20-01-2021	28-01-2021	20-02-2021	03-03-2021
29193	20-01-2021	10-02-2021	15-02-2021	19-03-2021
87596	25-02-2021	10-03-2021	25-03-2021
87549	02-03-2021	05-03-2021	06-03-2021	12-03-2021

And I think my restructured data will be as followed.

Phase ID |  Project ID |  Phase |     |Start    |   | End | 
1        |  22193      |  Start |     |18-1-2021|   |20-1-2021 
2        |  22193      |  Planning|   |20-1-2021|   |28-1-2021 
3        |  22193      |  Execition|  |28-1-2021|   |20-2-2021 
4        |  22193      |  Monitor |   |20-2-2021|   |03-03-2021 
5        |  22193      |  Live |      |         |   |03-03-2021 

 My question is: Is there a way to add phases in my data with every Project ID in Power BI (Dax, M Or tables?). Because doing it manualy whill take for ever.

And a sidequestion, will this restructuing my data affecy my sql database?😓

yours faithfully

[v-jingzhang · Accepted solution]

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.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-jingzhang]

Hi @Anonymous 

The data transformation and shape process in Power BI Desktop will not influence your sql database. It only queries data from data sources and then influences the data model in Power BI. 

In your data, does every project have 5 phases? For example, project 87596 has only 3 phases in your sample, so in the expected result, you want it to have only phase id 1 to 3, or 1 to 5 with null/blank values for 4&5? Or either one is ok?

Regards,
Community Support Team _ Jing

[Anonymous]

Hi @v-jingzhang,
Thank you for your replay.

Every project has in the end 5 phases but some projects can be in progress and thats why it has not the next phase (blank) 

[v-jingzhang · Accepted solution]

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.

[Anonymous]

Hi @v-jingzhang,
Thank you for your replay. this will help me continue my work.