my Report refused to refresh all of a sudden , upon ispection the mail said failed to refresh , what could possibly be , i changed format for few columns but no help , following is the code ,
let
// Combine the tables into a single source
Source = Table.Combine({#"South Metro", #"Great Southern", #"South West"}),
// Change types for specific columns and handle invalid data
#"Changed Type3" = Table.TransformColumns(
Source,
{
{"Job", each if Value.Is(_, type text) then _ else null, type text},
{"Contract Code", each if Value.Is(_, type text) then _ else null, type text},
{"Price", each try Number.From(_) otherwise 0.0, type number} // Handle Price column
}
),
// Filter rows where Contract Code is not null or empty
#"Filtered Rows1" = Table.SelectRows(#"Changed Type3", each [Contract Code] <> null and [Contract Code] <> ""),
// Add Is Overdue? column
#"Added Custom" = Table.AddColumn(#"Filtered Rows1", "Is Overdue?", each
if [Status] = "Completed" then "No"
else if [Status] = "Cancelled" then "No"
else if Date.From(DateTime.LocalNow()) > [KPI Date] then "Yes"
else "No",
type text),
// Add Issue Month column
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Issue Month", each Date.ToText([Issued Date], "MMM yyy"), type text),
// Add Issue Month Sort column
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Issue Month Sort", each Date.ToText([Issued Date], "yyyyMM")),
// Add ECD Month column
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "ECD Month", each Date.ToText([ECD], "MMM yyyy"), type text),
// Add ECD Month Sort column
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "ECD Month Sort", each Date.ToText([ECD], "yyyyMM")),
// Change types for sorting columns and handle invalid data
#"Changed Type" = Table.TransformColumns(
#"Added Custom4",
{
{"Issue Month Sort", each try Number.From(_) otherwise null, Int64.Type},
{"ECD Month Sort", each try Number.From(_) otherwise null, Int64.Type}
}
),
// Add This week Issue Date? column
#"Added Custom5" = Table.AddColumn(#"Changed Type", "This week Issue Date?", each Date.IsInCurrentWeek([Issued Date])),
// Change type for This week Issue Date? column
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom5", {{"This week Issue Date?", type logical}}),
// Add This week ECD? column
#"Added Custom6" = Table.AddColumn(#"Changed Type1", "This week ECD?", each Date.IsInCurrentWeek([ECD])),
// Change type for This week ECD? column
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom6", {{"This week ECD?", type logical}}),
// Merge with Lat Long table
#"Merged Queries" = Table.NestedJoin(#"Changed Type2", {"Address"}, #"Lat Long", {"Address"}, "Lat Long", JoinKind.LeftOuter),
// Expand Lat Long columns
#"Expanded Lat Long" = Table.ExpandTableColumn(#"Merged Queries", "Lat Long", {"Lat", "Long"}, {"Lat", "Long"}),
// Replace null values in Status column with "Not Started"
#"Replaced Value" = Table.ReplaceValue(#"Expanded Lat Long", null, "Not Started", Replacer.ReplaceValue, {"Status"}),
// Add DRC'd column
#"Added Custom8" = Table.AddColumn(#"Replaced Value", "DRC'd", each if [Status] = "Defect" then "Yes" else null, type text),
// Add Turnaround Time column
#"Added Custom7" = Table.AddColumn(#"Added Custom8", "Turnaround Time", each if [Status] = "Completed" then Duration.Days([ECD] - [Issued Date]) else null, Int64.Type),
// Diagnostic step to check Turnaround Time values
#"Diagnostic Turnaround Time" = Table.TransformColumns(#"Added Custom7", {"Turnaround Time", each try _ otherwise "Error"}),
// Add Turnaround Time Bucket column with refined error handling
#"Added Custom9" = Table.AddColumn(#"Diagnostic Turnaround Time", "Turnaround Time Bucket", each
try
let
turnaroundTime = [Turnaround Time]
in
if turnaroundTime = "Error" then "Error"
else if turnaroundTime = null then null
else if turnaroundTime < 15 then "0-14 Days"
else if turnaroundTime > 14 and turnaroundTime <= 28 then "15-28 Days"
else if turnaroundTime > 28 and turnaroundTime <= 90 then "29-90 Days"
else if turnaroundTime > 90 then "Over 90 Days"
else "NA"
otherwise
"Error", // or use another default value based on your preference
type text),
// Add Sort for Turnaround Buckets column with improved error handling
#"Added Custom10" = Table.AddColumn(#"Added Custom9", "Sort for Turnaround Buckets", each
try
let
bucket = [Turnaround Time Bucket]
in
if bucket = "0-14 Days" then 1
else if bucket = "15-28 Days" then 2
else if bucket = "29-90 Days" then 3
else if bucket = "Over 90 Days" then 4
else 5
otherwise
null, // or use another default value based on your preference
Int64.Type)
in
#"Added Custom10"
