let Source = PowerBI.Dataflows(null), #"ecddfa43-48d1-47e4-a609-1f59beff4c1c" = Source{[workspaceId="ecddfa43-48d1-47e4-a609-1f59beff4c1c"]}[Data], #"c89060e7-685b-4e16-b66f-741c72913fb8" = #"ecddfa43-48d1-47e4-a609-1f59beff4c1c"{[dataflowId="c89060e7-685b-4e16-b66f-741c72913fb8"]}[Data], FactOutagesAndInterruptions1 = #"c89060e7-685b-4e16-b66f-741c72913fb8"{[entity="FactOutagesAndInterruptions"]}[Data], #"Inserted Local Time" = Table.AddColumn(FactOutagesAndInterruptions1, "Local Time", each DateTimeZone.ToLocal([IncidentTime]), type datetimezone), #"Renamed Columns" = Table.RenameColumns(#"Inserted Local Time",{{"Local Time", "Outage Time (NZST)"}}), #"Inserted Local Time1" = Table.AddColumn(#"Renamed Columns", "Local Time", each DateTimeZone.ToLocal([ActualInterruptionTime]), type datetimezone), #"Renamed Columns1" = Table.RenameColumns(#"Inserted Local Time1",{{"Local Time", "Actual Interruption Start Time (NZST)"}}), #"Inserted Local Time2" = Table.AddColumn(#"Renamed Columns1", "Local Time", each DateTimeZone.ToLocal([ActualRestoreTime]), type datetimezone), #"Renamed Columns2" = Table.RenameColumns(#"Inserted Local Time2",{{"Local Time", "Actual Interruption Restore Time (NZST)"}}), #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns2",{"OverheadOrUnderGround"}), #"Inserted Local Time3" = Table.AddColumn(#"Removed Columns", "Local Time", each DateTimeZone.ToLocal([RetailConsumerDirectNotify]), type datetimezone), #"Renamed Columns3" = Table.RenameColumns(#"Inserted Local Time3",{{"Local Time", "Retail Consumer Direct Notify(NZST)"}}), #"Inserted Local Time4" = Table.AddColumn(#"Renamed Columns3", "Local Time", each DateTimeZone.ToLocal([NotifiedInterruptionDateTime]), type datetimezone), #"Renamed Columns4" = Table.RenameColumns(#"Inserted Local Time4",{{"Local Time", "Notified Interruption Date Time (NZST)"}}), #"Inserted Local Time5" = Table.AddColumn(#"Renamed Columns4", "Local Time", each DateTimeZone.ToLocal([NotifiedRestorationDateTime]), type datetimezone), #"Renamed Columns5" = Table.RenameColumns(#"Inserted Local Time5",{{"Local Time", "Notified Restoration Date Time (NZST)"}}), #"Inserted Local Time6" = Table.AddColumn(#"Renamed Columns5", "Local Time", each DateTimeZone.ToLocal([CancelledDate]), type datetimezone), #"Renamed Columns6" = Table.RenameColumns(#"Inserted Local Time6",{{"Local Time", "Cancelled Date (NZST)"}}), #"Inserted Local Time7" = Table.AddColumn(#"Renamed Columns6", "Local Time", each DateTimeZone.ToLocal([AlternativeDay]), type datetimezone), #"Renamed Columns7" = Table.RenameColumns(#"Inserted Local Time7",{{"Local Time", "Alternative Day (NZST)"}}), #"Inserted Local Time8" = Table.AddColumn(#"Renamed Columns7", "Local Time", each DateTimeZone.ToLocal([RetailConsumerRetailerNotify]), type datetimezone), #"Renamed Columns8" = Table.RenameColumns(#"Inserted Local Time8",{{"Local Time", "Retail Consumer Retailer Notify (NZST)"}}), #"Inserted Local Time9" = Table.AddColumn(#"Renamed Columns8", "Local Time", each DateTimeZone.ToLocal([DirectBilledConsumerNotify]), type datetimezone), #"Renamed Columns9" = Table.RenameColumns(#"Inserted Local Time9",{{"Local Time", "Direct Billed Consumer Notify (NZST)"}}), #"Inserted Local Time10" = Table.AddColumn(#"Renamed Columns9", "Local Time", each DateTimeZone.ToLocal([WebsitePublishing]), type datetimezone), #"Renamed Columns10" = Table.RenameColumns(#"Inserted Local Time10",{{"Local Time", "Website Publishing (NZST)"}, {"NewId", "Section ID"}, {"OutageID", "Outage ID"}}), #"Inserted Time" = Table.AddColumn(#"Renamed Columns10", "Time", each DateTime.Time([#"Actual Interruption Start Time (NZST)"]), type time), #"Renamed Columns11" = Table.RenameColumns(#"Inserted Time",{{"Time", "Interruption Start Time"}}), #"Inserted Time1" = Table.AddColumn(#"Renamed Columns11", "Time", each DateTime.Time([#"Actual Interruption Restore Time (NZST)"]), type time), #"Renamed Columns12" = Table.RenameColumns(#"Inserted Time1",{{"Time", "Interruption Restore Time"}}), #"Inserted Local Time11" = Table.AddColumn(#"Renamed Columns12", "Local Time", each DateTimeZone.ToLocal([CancellationNotified]), type datetimezone), #"Renamed Columns13" = Table.RenameColumns(#"Inserted Local Time11",{{"Local Time", "Cancellation Notified (NZST)"}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns13",{{"TotalOutageTime", Int64.Type}, {"NumberOfConsumers", Int64.Type}, {"LoadLost", Int64.Type}, {"InterruptionDuration", Int64.Type}, {"TotalConsumerMinutes", Int64.Type}, {"EnergyLost", type text}, {"TotalInterruptionTime", type duration}}), #"Inserted Date" = Table.AddColumn(#"Changed Type", "Date", each DateTime.Date([IncidentTime]), type date), #"Inserted Time2" = Table.AddColumn(#"Inserted Date", "Time", each DateTime.Time([ActualInterruptionTime]), type time), #"Renamed Columns14" = Table.RenameColumns(#"Inserted Time2",{{"Time", "Time (NZST)"}}), #"Inserted Date1" = Table.AddColumn(#"Renamed Columns14", "Date.1", each DateTime.Date([#"Actual Interruption Start Time (NZST)"]), type date), #"Removed Columns1" = Table.RemoveColumns(#"Inserted Date1",{"Date"}), #"Renamed Columns15" = Table.RenameColumns(#"Removed Columns1",{{"Date.1", "Date"}}), #"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns15",{"Time (NZST)"}), #"Inserted Time3" = Table.AddColumn(#"Removed Columns2", "Time", each DateTime.Time([#"Actual Interruption Start Time (NZST)"]), type time), #"Renamed Columns16" = Table.RenameColumns(#"Inserted Time3",{{"Time", "Time (NZST)"}}), #"Added Custom" = Table.AddColumn(#"Renamed Columns16", "Cancelled Events", each if ([#"Cancelled Date (NZST)"] <> null and Date.Year([#"Cancelled Date (NZST)"]) > 1900) or ( [Outage ID] <> null and [InterruptionDuration] > 0) then 1 else 0), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each true), #"Added Custom1" = Table.AddColumn(#"Filtered Rows", "Reportable?", each if ([Zone] = "8" or [Zone] = "6" or [Zone] = "4" or [Zone] = "2" ) or [NumberOfConsumers] = 0 or [Cancelled Events] = 0 or [CustomerRequested]="True" and (Text.Contains([Description],"CABLE FAULTED, LV") or Text.Contains([Description],"BLOWN LV FUSE") or Text.Contains([Description],"BURNT CONNECTOR LV") or Text.Contains([Description],"BLOWN LV FUSE, INTERMITTENT HIGH LOAD?") or Text.Contains([Description],"LINE DOWN, LV")) then 0 else 1), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Cancelled Events", Int64.Type}, {"Reportable?", Int64.Type}}), #"Added Custom2" = Table.AddColumn(#"Changed Type1", "Included?", each if [#"Cancelled Date (NZST)"] = null and [#"Actual Interruption Start Time (NZST)"] = null then 0 else if Date.Year([#"Cancelled Date (NZST)"]) > 1900 then 1 else if ([NumberOfConsumers] = 0 or [InterruptionDuration] = 0 or [CustomerRequested] = "Yes" or [Zone] = "8" or [Zone] = "6" or [Zone] = "4" or [Zone] = "2") then 0 else 1), #"Reordered Columns" = Table.ReorderColumns(#"Added Custom2",{"Id", "IncidentTime", "Zone", "FaultedPhases", "ZoneSubstation", "CircuitID", "Equipment", "Voltage", "Cause", "Nature", "Location", "Description", "Comments", "TotalOutageTime", "TotalConsumers", "PoleID", "JobID", "SubNetwork", "OHorUG", "Volts", "CancelledDateTime", "CancellationNotified", "Status", "CancellationReason", "CustomerRequested", "CommonDescription", "DistributionTransformer", "ActualInterruptionTime", "ActualRestoreTime", "NumberOfConsumers", "LoadLost", "InterruptionDuration", "TotalConsumerMinutes", "EnergyLost", "TotalInterruptionTime", "InterruptionType", "PlannedInterruptionType", "AlternativeDay", "RetailConsumerRetailerNotify", "RetailConsumerDirectNotify", "DirectBilledConsumerNotify", "WebsitePublishing", "ReasonForPlannedOutage", "TypeOfInterruptionAltDay", "AltDayReasonForChange", "Section ID", "Outage ID", "NotifiedInterruptionDateTime", "NotifiedRestorationDateTime", "Cancelled", "CancelledDate", "SectionCancellationReason", "RY", "DIS", "REGS", "DIS_REGS", "CalculatedOutageType", "Outage Time (NZST)", "Actual Interruption Start Time (NZST)", "Actual Interruption Restore Time (NZST)", "Retail Consumer Direct Notify(NZST)", "Notified Interruption Date Time (NZST)", "Notified Restoration Date Time (NZST)", "Cancelled Date (NZST)", "Alternative Day (NZST)", "Retail Consumer Retailer Notify (NZST)", "Direct Billed Consumer Notify (NZST)", "Website Publishing (NZST)", "Interruption Start Time", "Interruption Restore Time", "Cancellation Notified (NZST)", "Date", "Time (NZST)", "Included?", "Cancelled Events", "Reportable?"}), #"Added Custom3" = Table.AddColumn(#"Reordered Columns", "Reail Consumer Direct Notification", each if Date.Year([#"Retail Consumer Direct Notify(NZST)"]) > 1900 and Date.Year([#"Retail Consumer Direct Notify(NZST)"]) <> null then List.Count( List.Select( List.Difference( List.Dates(Date.From([#"Retail Consumer Direct Notify(NZST)"]), Number.From(Date.From([#"Actual Interruption Start Time (NZST)"]) - Date.From([#"Retail Consumer Direct Notify(NZST)"])) + 1, Duration.From(1)), List.Transform(#"NZ Holidays"[Day], Date.From) ), each Date.DayOfWeek(_, 1) < 5 ) ) else 0), #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom3",{{"Reail Consumer Direct Notification", Int64.Type}}), #"Added Custom4" = Table.AddColumn(#"Changed Type2", "Notice to Retailer (days)", each if Date.Year([#"Retail Consumer Retailer Notify (NZST)"]) > 1900 and Date.Year([#"Retail Consumer Retailer Notify (NZST)"]) <> null then List.Count( List.Select( List.Difference( List.Dates(Date.From([#"Retail Consumer Retailer Notify (NZST)"]), Number.From(Date.From([#"Actual Interruption Start Time (NZST)"]) - Date.From([#"Retail Consumer Retailer Notify (NZST)"])) + 1, Duration.From(1)), List.Transform(#"NZ Holidays"[Day], Date.From) ), each Date.DayOfWeek(_, 1) < 5 ) ) else 0), #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom4",{{"Notice to Retailer (days)", Int64.Type}}), #"Added Custom5" = Table.AddColumn(#"Changed Type3", "Notice on Website (days)", each if Date.Year([#"Website Publishing (NZST)"]) > 1900 and Date.Year([#"Website Publishing (NZST)"]) <> null then List.Count( List.Select( List.Difference( List.Dates(Date.From([#"Website Publishing (NZST)"]), Number.From(Date.From([#"Actual Interruption Start Time (NZST)"]) - Date.From([#"Website Publishing (NZST)"])) + 1, Duration.From(1)), List.Transform(#"NZ Holidays"[Day], Date.From) ), each Date.DayOfWeek(_, 1) < 5 ) ) else 0), #"Changed Type4" = Table.TransformColumnTypes(#"Added Custom5",{{"Notice on Website (days)", Int64.Type}}), #"Renamed Columns17" = Table.RenameColumns(#"Changed Type4",{{"Reail Consumer Direct Notification", "Reail Consumer Notification (days)"}}), #"Added Custom6" = Table.AddColumn(#"Renamed Columns17", "Notice Direct Bill Consumer (days)", each if Date.Year([#"Direct Billed Consumer Notify (NZST)"]) > 1900 and Date.Year([#"Direct Billed Consumer Notify (NZST)"]) <> null then List.Count( List.Select( List.Difference( List.Dates(Date.From([#"Direct Billed Consumer Notify (NZST)"]), Number.From(Date.From([#"Actual Interruption Start Time (NZST)"]) - Date.From([#"Direct Billed Consumer Notify (NZST)"])) + 1, Duration.From(1)), List.Transform(#"NZ Holidays"[Day], Date.From) ), each Date.DayOfWeek(_, 1) < 5 ) ) else 0), #"Changed Type5" = Table.TransformColumnTypes(#"Added Custom6",{{"Notice Direct Bill Consumer (days)", Int64.Type}}), #"Renamed Columns18" = Table.RenameColumns(#"Changed Type5",{{"Reail Consumer Notification (days)", "Notice to Retail Consumer (days)"}, {"Notice Direct Bill Consumer (days)", "Notice to Direct Bill Consumer (days)"}}), #"Added Custom7" = Table.AddColumn(#"Renamed Columns18", "(Reg) Notified Interruption", each let notice_cols_sum = [#"Notice to Retail Consumer (days)"] + [#"Notice to Retailer (days)"] + [#"Notice on Website (days)"] + [#"Notice to Direct Bill Consumer (days)"], direct_cust_notice = if [#"Notice to Retail Consumer (days)"] <> 0 then [#"Notice to Retail Consumer (days)"] >= 4 else true , retail_notice = if [#"Notice to Retailer (days)"] <> 0 then [#"Notice to Retailer (days)"] >= 10 else true , direct_bill_cust_notice = if [#"Notice to Direct Bill Consumer (days)"] <> 0 then [#"Notice to Direct Bill Consumer (days)"] >= 10 else true , website_notice = if [#"Notice on Website (days)"] <> 0 then [#"Notice on Website (days)"] >= 10 else true in if [InterruptionType] = "1" or List.Contains({null,""},[InterruptionType]) then null else if [InterruptionType] = "3" then false else if notice_cols_sum = 0 then false else if direct_bill_cust_notice and direct_cust_notice and retail_notice and website_notice then true else false), #"Added Custom8" = Table.AddColumn(#"Added Custom7", "Raw SAIDI", each let ICP = 19577 in [InterruptionDuration] * [NumberOfConsumers] / ICP), #"Changed Type6" = Table.TransformColumnTypes(#"Added Custom8",{{"Raw SAIDI", type number}}), #"Added Custom9" = Table.AddColumn(#"Changed Type6", "Raw SAIFI", each let ICP = 91577 in [NumberOfConsumers] / ICP), #"Changed Type7" = Table.TransformColumnTypes(#"Added Custom9",{{"Raw SAIFI", type number}}), #"Added Custom10" = Table.AddColumn(#"Changed Type7", "Notified_Int_Month", each if [InterruptionType] = "2" then if Date.Year([#"Cancelled Date (NZST)"]) > 1900 then Date.MonthName([#"Cancelled Date (NZST)"]) else Date.MonthName([#"Actual Interruption Start Time (NZST)"]) else null), #"Filtered Rows1" = Table.SelectRows(#"Added Custom10", each ([#"Reportable?"] = 1)), #"Inserted Maximum" = Table.AddColumn(#"Filtered Rows1", "Maximum", each if [InterruptionType] ="1" then null else if Date.Year([#"Cancelled Date (NZST)"]) = 1900 and [InterruptionType] = "2" then List.Max({[#"Notice to Retail Consumer (days)"], [#"Notice to Direct Bill Consumer (days)"]}) else if Date.Year([#"Cancelled Date (NZST)"]) = 1900 and [InterruptionType] = "3" and Date.Year([#"Notified Interruption Date Time (NZST)"]) <> 1900 then List.Max({[#"Notice to Retail Consumer (days)"], [#"Notice to Direct Bill Consumer (days)"]}) else null), #"Renamed Columns19" = Table.RenameColumns(#"Inserted Maximum",{{"Maximum", "Customer Notification (days)"}}), #"Added Custom11" = Table.AddColumn(#"Renamed Columns19", "10 Day Notification Issued", each if [#"Customer Notification (days)"] < 10 then 1 else if [#"Customer Notification (days)"] = null then null else 0) in #"Added Custom11"