Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a need to greate a GANTT chart from data that is not, currently well structured for it.
In our data there can be up to 5 phases for a given project. However, all phases are listed in a single project record. The record does include the phase type and the start and end dates. We have 20 possible phase types. A single project can have 1 to 5 phases in any order. In other words, a given phase may be the only phase for a project or it may be the first or it may be the third.
I believe that I need to unpivot the data dynamically so that I have records for each project-phase along with the dates for that phase. However I have no idea how to unpivot multiple columns into one - if that is, indeed, what I need to do.
This is a rough exmaple of what the data can look like. Again, the desired output would be to have a GANTT chart with each phase charted by project.
Project | Type1 | Start date1 | End Date1 | Type2 | Start Date2 | End Date2 | Type3 | Start Date3 | end Date3 | Type4 | Start Date4 | End Date4 | Type5 | Start Date5 | EndDate5 |
123abc | Flat | 1/1/2024 | 1/12/2024 | ||||||||||||
456def | Sheet Metal | 2/23/2024 | 3/4/2024 | Sigle ply | 3/5/2024 | 3/13/2024 | Foam | 3/18/2024 | 3/22/2024 | Coating | 3/24/2024 | 4/2/2024 | Tile | 4/3/2024 | 4/4/2024 |
789ghi | Single | 1/18/2024 | 2/12/2024 | Foam | 2/13/2024 | 2/28/2024 |
Solved! Go to Solution.
Here's a Power Query solution. Overview:
1. Unpivot all columns except Project
2. Split column Attribute (Non-Digit to Digit)
3. Pivot column Attribute.1 (Values Column = Value; Aggregate Value Function = Don’t Aggregate)
4. Filter out blanks
I added a few steps to clean up inconsistent spelling.
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"lVA9D8IgFPwrhLkJ8qBaZ5NuTnUjDKhISbB1YPHfi1A+FgcTCPfuLvcuCIEpMHW94Q6PTvnwUEIJ7IAnCBmjv67sBOb9/q4fYZxmrT06a69cmEIiy6GM8Awna5xGL/eOdF8dtLjHVT0TNVQZSsPTqrxdTGJLbgAZXqzTkWFV3HzfvofhaGYbqywmOmmzCZq/2IpA0y1oxfrzSPkB",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [
Project = _t,
Type1 = _t,
#"Start date1" = _t,
#"End Date1" = _t,
Type2 = _t,
#"Start Date2" = _t,
#"End Date2" = _t,
Type3 = _t,
#"Start Date3" = _t,
#"end Date3" = _t,
Type4 = _t,
#"Start Date4" = _t,
#"End Date4" = _t,
Type5 = _t,
#"Start Date5" = _t,
EndDate5 = _t
]
),
ChangeType = Table.TransformColumnTypes(
Source,
{
{"Project", type text},
{"Type1", type text},
{"Start date1", type date},
{"End Date1", type date},
{"Type2", type text},
{"Start Date2", type date},
{"End Date2", type date},
{"Type3", type text},
{"Start Date3", type date},
{"end Date3", type date},
{"Type4", type text},
{"Start Date4", type date},
{"End Date4", type date},
{"Type5", type text},
{"Start Date5", type date},
{"EndDate5", type date}
}
),
UnpivotColumns = Table.UnpivotOtherColumns(ChangeType, {"Project"}, "Attribute", "Value"),
SplitColumn = Table.SplitColumn(
UnpivotColumns,
"Attribute",
Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0" .. "9"}, c), {"0" .. "9"}),
{"Attribute.1", "Attribute.2"}
),
ReplaceValue = Table.ReplaceValue(
SplitColumn,
"Start date",
"Start Date",
Replacer.ReplaceText,
{"Attribute.1"}
),
ReplaceValue2 = Table.ReplaceValue(
ReplaceValue,
"EndDate",
"End Date",
Replacer.ReplaceText,
{"Attribute.1"}
),
ReplaceValue3 = Table.ReplaceValue(
ReplaceValue2,
"end Date",
"End Date",
Replacer.ReplaceText,
{"Attribute.1"}
),
RenameColumn = Table.RenameColumns(ReplaceValue3, {{"Attribute.2", "Phase"}}),
PivotColumns = Table.Pivot(
RenameColumn,
List.Distinct(RenameColumn[Attribute.1]),
"Attribute.1",
"Value"
),
FilterRows = Table.SelectRows(PivotColumns, each ([Type] <> "" and [Type] <> " ")),
ChangeType2 = Table.TransformColumnTypes(
FilterRows,
{{"Type", type text}, {"Start Date", type date}, {"End Date", type date}}
)
in
ChangeType2
Proud to be a Super User!
Here's a Power Query solution. Overview:
1. Unpivot all columns except Project
2. Split column Attribute (Non-Digit to Digit)
3. Pivot column Attribute.1 (Values Column = Value; Aggregate Value Function = Don’t Aggregate)
4. Filter out blanks
I added a few steps to clean up inconsistent spelling.
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"lVA9D8IgFPwrhLkJ8qBaZ5NuTnUjDKhISbB1YPHfi1A+FgcTCPfuLvcuCIEpMHW94Q6PTvnwUEIJ7IAnCBmjv67sBOb9/q4fYZxmrT06a69cmEIiy6GM8Awna5xGL/eOdF8dtLjHVT0TNVQZSsPTqrxdTGJLbgAZXqzTkWFV3HzfvofhaGYbqywmOmmzCZq/2IpA0y1oxfrzSPkB",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [
Project = _t,
Type1 = _t,
#"Start date1" = _t,
#"End Date1" = _t,
Type2 = _t,
#"Start Date2" = _t,
#"End Date2" = _t,
Type3 = _t,
#"Start Date3" = _t,
#"end Date3" = _t,
Type4 = _t,
#"Start Date4" = _t,
#"End Date4" = _t,
Type5 = _t,
#"Start Date5" = _t,
EndDate5 = _t
]
),
ChangeType = Table.TransformColumnTypes(
Source,
{
{"Project", type text},
{"Type1", type text},
{"Start date1", type date},
{"End Date1", type date},
{"Type2", type text},
{"Start Date2", type date},
{"End Date2", type date},
{"Type3", type text},
{"Start Date3", type date},
{"end Date3", type date},
{"Type4", type text},
{"Start Date4", type date},
{"End Date4", type date},
{"Type5", type text},
{"Start Date5", type date},
{"EndDate5", type date}
}
),
UnpivotColumns = Table.UnpivotOtherColumns(ChangeType, {"Project"}, "Attribute", "Value"),
SplitColumn = Table.SplitColumn(
UnpivotColumns,
"Attribute",
Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0" .. "9"}, c), {"0" .. "9"}),
{"Attribute.1", "Attribute.2"}
),
ReplaceValue = Table.ReplaceValue(
SplitColumn,
"Start date",
"Start Date",
Replacer.ReplaceText,
{"Attribute.1"}
),
ReplaceValue2 = Table.ReplaceValue(
ReplaceValue,
"EndDate",
"End Date",
Replacer.ReplaceText,
{"Attribute.1"}
),
ReplaceValue3 = Table.ReplaceValue(
ReplaceValue2,
"end Date",
"End Date",
Replacer.ReplaceText,
{"Attribute.1"}
),
RenameColumn = Table.RenameColumns(ReplaceValue3, {{"Attribute.2", "Phase"}}),
PivotColumns = Table.Pivot(
RenameColumn,
List.Distinct(RenameColumn[Attribute.1]),
"Attribute.1",
"Value"
),
FilterRows = Table.SelectRows(PivotColumns, each ([Type] <> "" and [Type] <> " ")),
ChangeType2 = Table.TransformColumnTypes(
FilterRows,
{{"Type", type text}, {"Start Date", type date}, {"End Date", type date}}
)
in
ChangeType2
Proud to be a Super User!
User | Count |
---|---|
93 | |
87 | |
77 | |
72 | |
66 |
User | Count |
---|---|
115 | |
107 | |
86 | |
65 | |
64 |