Hi Folks
Apologies for submitting my report without some comments as was trying to beat this CH19 deadline and other commitments. I’m sure many will agree with me.
My thought process for the challenge are:-
- Retrieval of the various years data by first creating a sample retrieval for 2018 so that I can built a function in Power Query to retrieve data for 2019 to 2021. Below is the script of the function:-
(SourceFile as text) =>
let
Source = Excel.Workbook(File.Contents(SourceFile), null, true),
#“Sheet1” = Source{[Item=“Sheet1”,Kind=“Sheet”]}[Data],
#“Promoted Headers” = Table.PromoteHeaders(#“Sheet1”, [PromoteAllScalars=true]),
#“Changed Type” = Table.TransformColumnTypes(#“Promoted Headers”,{{“CallTimestamp”, type any}, {“Call Type”, Int64.Type}, {“EmployeeID”, type text}, {“CallDuration”, Int64.Type}, {“WaitTime”, Int64.Type}, {“CallAbandoned”, Int64.Type}}),
#“Removed Columns” = Table.RemoveColumns(#“Changed Type”,{“CallAbandoned”}),
#“Split Column by Delimiter” = Table.SplitColumn(Table.TransformColumnTypes(#“Removed Columns”, {{“CallTimestamp”, type text}}, “en-AU”), “CallTimestamp”, Splitter.SplitTextByEachDelimiter({" “}, QuoteStyle.None, false), {“CallTimestamp.1”, “CallTimestamp.2”}),
#“Split Column by Delimiter1” = Table.SplitColumn(#“Split Column by Delimiter”, “CallTimestamp.1”, Splitter.SplitTextByDelimiter(”/", QuoteStyle.None), {“CallTimestamp.1.1”, “CallTimestamp.1.2”, “CallTimestamp.1.3”}),
#“Changed Type1” = Table.TransformColumnTypes(#“Split Column by Delimiter1”,{{“CallTimestamp.1.1”, Int64.Type}, {“CallTimestamp.1.2”, Int64.Type}, {“CallTimestamp.1.3”, Int64.Type}, {“CallTimestamp.2”, type time}}),
#“Renamed Columns” = Table.RenameColumns(#“Changed Type1”,{{“CallTimestamp.1.1”, “MonthNo”}, {“CallTimestamp.1.2”, “Date”}, {“CallTimestamp.1.3”, “Year”}, {“CallTimestamp.2”, “CallTimestamp”}}),
#“Added Custom” = Table.AddColumn(#“Renamed Columns”, “TotalCallTime (Mins)”, each Number.Round( ([CallDuration] + [WaitTime]) / 60, 2)),
#“Changed Type2” = Table.TransformColumnTypes(#“Added Custom”,{{“TotalCallTime (Mins)”, type number}})
in
#“Changed Type2”
-
Subsequently combining 2018 to 2021 tables together to create the fact table. Although there’re some twist to dates in the last set of data which I’d to change.
-
Creating the Dimension Tables
-
Selecting the theme/colour of the report. I like a contrasting colours, Matrix inspired colours
-
I liked the settings/navigation panel created by others in this challenge e.g. the Throphies created by jafernandezpuga, but had to give it a miss this time
-
Opted for (preference for large graphics in this CH)
i. Summary page, main features waterfall on YoY with drill-through to (ii)
ii. Operators Performance Analysis, Top/Bottom Operators
iii. Management Performance Analysis, main feature bar graph that shows various trends
- Main regrets
i. Forgot to hide my tooltips & format them properly, month not in sequence
ii. Should have included all Bins (only manage to add Bin3 in the Summary) in a series switch panels
iii. More time granularity analysis and time pressed to include SLA (fully aware of it)
Great to see so many Talents here! 
