Gantt table where Milestones from another tab are in one row

[janboo]

Hi,
First, thank you in advance for your help! Will be so grateful if you can help!

I have a list of Types (~ over 500 lines) broken down by environment and release dates.
A type will never have the same release date per environment. The values are listed in Tab1.

Migration Team	Migration Partner	Type	Environment	Release Date
A	Tech	Apple (AIID201819)	Dev	10-17-22
A	Dev	Banana (AIID204379)	Dev	11-30-22
B	Dev	Apple (AIID201819)	QA	11-17-22
A	Tech	Apple (AIID201819)	UAT	11-30-22
A	Dev	Orange (AIID222222)	Test	12-01-22
A	Dev	Apple (AIID201819)	PreProd	12-15-22
B	Dev	Apple (AIID201819)	Stage	12-30-22
A	Dev	Banana (AIID204379)	Prod	12-30-22

I want to create a gantt chart where a Type will have the environments across a single line, under the year/month/week the release is scheduled. Tab2.

Timeline

Oct-22

Nov-22

Dec-22

Week

10-03-22

10-10-22​

10-17-22​

10-24-22​

10-31-22​

11-07-22

11-14-22​

11-21-22​

11-28-22​

12-05-22​

12-12-22

12-19-22​

12-26-22​

01-02-23​

01-09-23​

01-16-23

Apple (AIID201819)

Dev

QA

UAT

PreProd

Stage

Banana (AIID204379)

Dev

Prod

Orange (AIID222222)

Test

Is there an easier way than having a string of formulas? Macro please? Anything?

Thanks again for your help!!

Jan

 

[AlanSidman]

A power query solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Release", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Type"}, {{"Data", each _, type table [Migration Team=text, Migration Partner=text, Type=text, Environment=text, Release=nullable date]}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Environment", "Release"}, {"Data.Environment", "Data.Release"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Data", {{"Data.Release", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded Data", {{"Data.Release", type text}}, "en-US")[Data.Release]), "Data.Release", "Data.Environment")
in
    #"Pivoted Column"

 

[janboo]

You are truly a lifesaver Alan Sidman!! Thank you sincerely!

 

[janboo]

Hi AlanSidman,
I didn't notice until now, is it possible to have the dates in order in tab2 please?


Thanks,
Jan

 

[AlanSidman]

I missed that also.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Release", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Type"}, {{"Data", each _, type table [Migration Team=text, Migration Partner=text, Type=text, Environment=text, Release=nullable date]}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Environment", "Release"}, {"Data.Environment", "Data.Release"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Data",{{"Data.Release", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Sorted Rows", {{"Data.Release", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Sorted Rows", {{"Data.Release", type text}}, "en-US")[Data.Release]), "Data.Release", "Data.Environment")
in
    #"Pivoted Column"

 

[janboo]

I missed that also.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Release", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Type"}, {{"Data", each _, type table [Migration Team=text, Migration Partner=text, Type=text, Environment=text, Release=nullable date]}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Environment", "Release"}, {"Data.Environment", "Data.Release"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Data",{{"Data.Release", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Sorted Rows", {{"Data.Release", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Sorted Rows", {{"Data.Release", type text}}, "en-US")[Data.Release]), "Data.Release", "Data.Environment")
in
    #"Pivoted Column"

Thank you sincerely Alan!!!