Forum Discussion
Data help and tips
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
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.
5 Replies
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 _ JingHi 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)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.