Forum Discussion
timeline of ongoing projects
Hello all,
I am a complete Power BI newbie and therefore need your help.
I am currently trying to create an overview of all projects and their different phases (based on a Gantt chart). So from this it should be possible to see which project is still in the development phase and which ones are already in the production phase. The lines would refer to a single project and represent several bars that contain the project phases. So it would show the timeframe of the project acquisition, the development and the production phase of each project.
For a Gantt chart, in addition to the start date, you need at least the duration of a phase or the end date. In my case the end date corresponds to the start date of the next phase. This means:
- RFI Phase: Start = RFI Date; End = RFQ Date
- RFQ Phase: Start = RFQ Date; End = DOS Date
- Development Phase: Start = DOS; End = SOP
- Production Phase: Start = SOP; End = EOP
The solution should look like this:
However, my table does not have a column called "Start Date" and one called "End Date" or "Duration", but RFI, RFQ, DOS, SOP and EOP with exact dates like the extract below.
I recently got the hint to add a column with the formula {[RFQ]..{EOP]} in Power Query to get all valid days in one column and then divide them into phases in a second step. Unfortunately, the result has created more than 70,000 new rows for me and is constantly crashing my power bi file.
Can you give me an advice on how to restructure this so that it can be displayed with a Gantt chart?
Thanks a lot for your help!
Hi Anonymous ,
You can try this query to transform your data:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIyMDLQN9c3hDENDWBsQ31jfWMo20Tf0BAmbqkPYsbqRCs5wRQaIfQgMU304botYUygTUYgU2NjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Group = _t, RFI = _t, RFQ = _t, DOS = _t, SOP = _t, EOP = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"RFI", type date}, {"RFQ", type date}, {"DOS", type date}, {"SOP", type date}, {"EOP", type date}, {"Group", type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Group"}, "Attribute", "Value"), #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Group"}, {{"Data", each let tab=Table.AddIndexColumn(_,"Index",1,1,Int64.Type) in Table.AddColumn(tab,"New",(x)=>try Table.Max( Table.SelectRows(tab,(y)=>y[Index]=x[Index]-1),"Index")[Value] otherwise (x)[Value] ), type table [Group=nullable text, Attribute=text, Value=date]}}), #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Attribute", "Value","New"}, {"Data.Attribute", "Data.Value","Data.New"}), #"Renamed Columns" = Table.RenameColumns(#"Expanded Data",{{"Data.Attribute", "Project State"}, {"Data.Value", "End Date"}, {"Data.New", "Start Date"}}), #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Project State] <> "RFI")), #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Group", type text}, {"Project State", type text}, {"End Date", type date}, {"Start Date", type date}}) in #"Changed Type1"As far as I know, Gantt chart could not achieve the same result as your shown, the result after the query would be like this:
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
v-yingjlCommunity Support
Hi Anonymous ,
You can try this query to transform your data:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIyMDLQN9c3hDENDWBsQ31jfWMo20Tf0BAmbqkPYsbqRCs5wRQaIfQgMU304botYUygTUYgU2NjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Group = _t, RFI = _t, RFQ = _t, DOS = _t, SOP = _t, EOP = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"RFI", type date}, {"RFQ", type date}, {"DOS", type date}, {"SOP", type date}, {"EOP", type date}, {"Group", type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Group"}, "Attribute", "Value"), #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Group"}, {{"Data", each let tab=Table.AddIndexColumn(_,"Index",1,1,Int64.Type) in Table.AddColumn(tab,"New",(x)=>try Table.Max( Table.SelectRows(tab,(y)=>y[Index]=x[Index]-1),"Index")[Value] otherwise (x)[Value] ), type table [Group=nullable text, Attribute=text, Value=date]}}), #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Attribute", "Value","New"}, {"Data.Attribute", "Data.Value","Data.New"}), #"Renamed Columns" = Table.RenameColumns(#"Expanded Data",{{"Data.Attribute", "Project State"}, {"Data.Value", "End Date"}, {"Data.New", "Start Date"}}), #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Project State] <> "RFI")), #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Group", type text}, {"Project State", type text}, {"End Date", type date}, {"Start Date", type date}}) in #"Changed Type1"As far as I know, Gantt chart could not achieve the same result as your shown, the result after the query would be like this:
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.