Setting up Data for Gantt Chart

[amanda_wahlig]

I am working to take basic data entered by employees  and transform it in BI into a Gantt Chart.

The List populated/updated by the employees will be something like this:

Project	Start Date	End Phase 1	Start Phase 2	End Phase 3	Start Phase 4	End Phase 4
A	1/1/23	1/15/23	1/16/23	1/31/23	2/1/23	3/3/23
B	11/6/23	11/30/23	12/1/23	12/15/23	12/16/23	1/4/24
C	12/3/23	12/7/23	12/8/23	12/31/23	1/2/24	1/15/24

I would like to show a chart that visually breaks down the entire timeline for project by phases, but having BI create the child rows to the parent row (to avoid having the staff have to enter/create all the child rows). Can you just point me in the direction of the steps to take in BI to do this?

[Anonymous · Accepted solution]

Hi @amanda_wahlig,

I think this type of table structure should not be suitable of processed in Gantt chart.

I'd like to suggest you to unpivot and transform these columns to convert them to attribute and two date fields, then you can simply analysis these records in Gantt chart.

Raw table:

Full query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Rc7BDYAwCIXhXTg3QaBVr+oYpPuvYbHlefsSfgjudFEhYWG1iQbtKVtTzczYAr043VEIZzvibRF1qP3E2cpavxvPnBiaAzohw4sae/nruNBf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, #"Start Date" = _t, #"End Phase 1" = _t, #"Start Phase 2" = _t, #"End Phase 3" = _t, #"Start Phase 4" = _t, #"End Phase 4" = _t]),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Start Date", "Start Phase 1"}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Project"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Attribute", "Step"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Project", type text}, {"Attribute", type text}, {"Step", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Project", "Step"}, {{"Content", each Table.PromoteHeaders(Table.Transpose(Table.SelectColumns(_,{"Attribute","Value"})), [PromoteAllScalars=true]), type table}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Content", {"Start Phase", "End Phase"}, {"Start Phase", "End Phase"})
in
    #"Expanded Count"

Result:

Regards,
Xiaoxin Sheng