I have a fairly complex query that I have been runnin gfor the past 12 weeeks. I was hit with this error yesterday and cannot figure out what's causing it.
This is the advanced editor code for the query:
let
Source = Sql.Database("comunityhealth.database.windows.net,1433", "prod_NICD_20200507"),
dbo_PatientCaseRecordForm = Source{[Schema="dbo",Item="PatientCaseRecordForm"]}[Data],
#"Expanded Patient" = Table.ExpandRecordColumn(dbo_PatientCaseRecordForm, "Patient", {"PatientDischargeForm", "PatientStatus"}, {"PatientDischargeForm", "PatientStatus"}),
#"Expanded PatientStatus" = Table.ExpandRecordColumn(#"Expanded Patient", "PatientStatus", {"Name"}, {"PatientStatus.Name"}),
#"Filtered Rows2" = Table.SelectRows(#"Expanded PatientStatus", each ([PatientStatus.Name] <> "New")),
#"Bulk Rename" = Table.RenameColumns(#"Filtered Rows2",{{"ChronicCardiac", "Chronic Cardiac"}, {"ChronicPulmonary", "Chronic Pulmonary"}, {"ChronicKidney", "Chronic Kidney"}, {"MalignantNeoplasm", "Malignant Neoplasm"}, {"HivNoART", "HIV no ART"}, {"HivARTWithViral", "HIV + ART + Viral Sup."}, {"HivARTNoViral", "HIV + ART"}, {"ArvCombo", "ARV Combination"}, {"ArvTLD", "ARV TLD"}, {"AceInhibitor", "ACE Inhibitor"}, {"AntiInflammatory", "Non-Storoidal anti-inflammatory"}, {"SoreThroat", "Sore Throat"}, {"MuscleAches", "Muscle Aches"}, {"JointPain", "Joint Pain"}, {"BreathShortness", "Shortness of Breath"}, {"AbdominalPain", "Abdominal Pain"}, {"OtherSymptoms", "Other Symptoms"}, {"OnOxygen", "On Oxygen"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Bulk Rename",{{"On Oxygen", Int64.Type}, {"Ventilated", Int64.Type}}),
#"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"AdmissionDate", type text}}, "en-GB"), "AdmissionDate", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"AdmissionDate.1", "AdmissionDate.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"AdmissionDate.1", type date}, {"AdmissionDate.2", type time}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"Created", "AdmissionDate.1", "On Oxygen", "Ventilated", "PatientPatientId", "WardType"}),
#"Split Column by Delimiter1" = Table.SplitColumn(Table.TransformColumnTypes(#"Removed Other Columns", {{"Created", type text}}, "en-GB"), "Created", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Created.1", "Created.2"}),
#"Renamed Columns2" = Table.RenameColumns(#"Split Column by Delimiter1",{{"Created.1", "Created"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns2",{"Created.2"}),
#"Expanded WardType" = Table.ExpandRecordColumn(#"Removed Columns", "WardType", {"Name"}, {"WardType.Name"}),
#"Added Custom2" = Table.AddColumn(#"Expanded WardType", "WardValue", each 1),
#"Added Custom" = Table.AddColumn(#"Added Custom2", "Source", each "Adm Form"),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "ARDS", each 0),
#"Replaced Value" = Table.ReplaceValue(#"Added Custom1",null,"General Ward",Replacer.ReplaceValue,{"WardType.Name"}),
#"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[WardType.Name]), "WardType.Name", "WardValue", List.Sum),
#"Replaced Value1" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"General Ward", "Isolation Ward", "Intensive Care Unit", "High care"}),
#"Reordered Columns" = Table.ReorderColumns(#"Replaced Value1",{"AdmissionDate.1", "On Oxygen", "Ventilated", "ARDS", "Intensive Care Unit", "High care", "General Ward", "Isolation Ward", "PatientPatientId", "Source"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"AdmissionDate.1", "Date"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"On Oxygen", Int64.Type}, {"Ventilated", Int64.Type}, {"ARDS", Int64.Type}, {"Intensive Care Unit", Int64.Type}, {"High care", Int64.Type}, {"General Ward", Int64.Type}, {"Isolation Ward", Int64.Type}, {"Created", type date}}),
#"Appended Query" = Table.Combine({#"Changed Type2", #"Daily Severities", #"Discharge Severities"}),
#"Renamed Columns1" = Table.RenameColumns(#"Appended Query",{{"Date", "Form Date"}}),
#"Merged Queries3" = Table.NestedJoin(#"Renamed Columns1", {"PatientPatientId"}, Patient, {"PatientId"}, "Patient", JoinKind.LeftOuter),
#"Expanded Patient2" = Table.ExpandTableColumn(#"Merged Queries3", "Patient", {"Facility Type"}, {"Facility Type"}),
#"Filtered Rows3" = Table.SelectRows(#"Expanded Patient2", each ([Facility Type] <> "NURSING HOME")),
#"Sorted Rows" = Table.Sort(#"Filtered Rows3",{{"PatientPatientId", Order.Ascending}, {"Form Date", Order.Ascending}}),
#"Added Custom8" = Table.AddColumn(#"Sorted Rows", "SeverityDesc.", each if [High care] = 1 then "Severe" else if [Intensive Care Unit] = 1 then "Severe" else if [Ventilated] = 1 then "Severe" else if [ARDS] = 1 then "Severe" else if [On Oxygen] = 1 then "Moderate" else if [Source] = "Discharge Form" then "Discharged" else "Mild"),
#"Reordered Columns1" = Table.ReorderColumns(#"Added Custom8",{"On Oxygen", "Ventilated", "ARDS", "Intensive Care Unit", "High care", "General Ward", "Isolation Ward", "Form Date", "Created", "PatientPatientId", "Source", "SeverityDesc."}),
#"Added Custom6" = Table.AddColumn(#"Reordered Columns1", "Today", each Date.From(DateTime.LocalNow())),
#"Table Buffer" = Table.Buffer(#"Added Custom6"),
#"Added Index" = Table.AddIndexColumn(#"Table Buffer", "Index", 0, 1),
#"Added Custom3" = Table.AddColumn(#"Added Index", "Index+1", each [Index]+1),
#"Merged Queries" = Table.NestedJoin(#"Added Custom3", {"Index+1"}, #"Added Custom3", {"Index"}, "Added Custom3", JoinKind.LeftOuter),
#"Expanded Added Custom3" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom3", {"Form Date", "PatientPatientId"}, {"Form Date.1", "PatientPatientId.1"}),
#"Added Custom4" = Table.AddColumn(#"Expanded Added Custom3", "To Date", each if [PatientPatientId.1] = [PatientPatientId]
then [Form Date.1]
else Date.From(DateTime.LocalNow())),
#"Added Custom5" = Table.AddColumn(#"Added Custom4", "Form Date Normalised", each if [Form Date] is null and [Form Date.1] is null
then [Created] else if [Form Date] is null then [Form Date.1]
else [Form Date]),
#"Invoked Custom Function" = Table.AddColumn(#"Added Custom5", "DatesBetween", each DatesBetween([Form Date Normalised], [To Date], "Day")),
#"Changed Type3" = Table.TransformColumnTypes(#"Invoked Custom Function",{{"To Date", type date}, {"Form Date Normalised", type date}, {"Today", type date}}),
#"Expanded DatesBetween" = Table.ExpandListColumn(#"Changed Type3", "DatesBetween"),
#"Changed Type4" = Table.TransformColumnTypes(#"Expanded DatesBetween",{{"DatesBetween", type date}, {"On Oxygen", Int64.Type}, {"Ventilated", Int64.Type}, {"ARDS", Int64.Type}, {"Intensive Care Unit", Int64.Type}, {"High care", Int64.Type}, {"General Ward", Int64.Type}, {"Isolation Ward", Int64.Type}, {"Index+1", Int64.Type}}),
#"Added Custom10" = Table.AddColumn(#"Changed Type4", "SubOrdDataPoint", each if [To Date] = [Today] then 0 else if [DatesBetween] = [To Date] then 1 else 0),
#"Filtered Rows" = Table.SelectRows(#"Added Custom10", each ([SubOrdDataPoint] = 0)),
#"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows",{"On Oxygen", "Ventilated", "ARDS", "Intensive Care Unit", "High care", "General Ward", "Isolation Ward", "PatientPatientId", "Source", "SeverityDesc.", "Today", "To Date", "Form Date Normalised", "DatesBetween", "SubOrdDataPoint"}),
#"Merged Queries1" = Table.NestedJoin(#"Removed Other Columns1", {"PatientPatientId"}, PatientDischargeForm, {"Patient.PatientId"}, "PatientDischargeForm", JoinKind.LeftOuter),
#"Expanded PatientDischargeForm" = Table.ExpandTableColumn(#"Merged Queries1", "PatientDischargeForm", {"OutcomeDate"}, {"OutcomeDate"}),
#"Split Column by Delimiter2" = Table.SplitColumn(Table.TransformColumnTypes(#"Expanded PatientDischargeForm", {{"OutcomeDate", type text}}, "en-GB"), "OutcomeDate", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"OutcomeDate.1", "OutcomeDate.2", "OutcomeDate.3"}),
#"Removed Columns1" = Table.RemoveColumns(#"Split Column by Delimiter2",{"OutcomeDate.2", "OutcomeDate.3"}),
#"Changed Type5" = Table.TransformColumnTypes(#"Removed Columns1",{{"OutcomeDate.1", type date}}),
#"Disch<Other fix" = Table.AddColumn(#"Changed Type5", "DischargeDelete", each if [OutcomeDate.1] = null then 0 else if [DatesBetween] >= [OutcomeDate.1] then 1 else 0),
#"Added Custom9" = Table.AddColumn(#"Disch<Other fix", "SourceHeirachySort", each if [Source] = "Adm Form" then 2 else if [Source] = "Daily Assessment" then 1 else 3),
#"Sorted Rows1" = Table.Sort(#"Added Custom9",{{"PatientPatientId", Order.Ascending}, {"SourceHeirachySort", Order.Ascending}, {"DatesBetween", Order.Ascending}}),
#"Table Buffer2" = Table.Buffer(#"Sorted Rows1"),
#"Dup Remove" = Table.Distinct(#"Table Buffer2", {"DatesBetween", "PatientPatientId"}),
#"Filtered Rows1" = Table.SelectRows(#"Dup Remove", each ([DischargeDelete] = 0)),
#"Merged Queries2" = Table.NestedJoin(#"Filtered Rows1", {"PatientPatientId"}, Patient, {"PatientId"}, "Patient", JoinKind.LeftOuter),
#"Expanded Patient1" = Table.ExpandTableColumn(#"Merged Queries2", "Patient", {"Facility", "PatientStatus.Name"}, {"Facility", "PatientStatus.Name"}),
#"Removed Other Columns2" = Table.SelectColumns(#"Expanded Patient1",{"On Oxygen", "Ventilated", "ARDS", "Intensive Care Unit", "High care", "General Ward", "Isolation Ward", "PatientPatientId", "Source", "SeverityDesc.", "DatesBetween", "Facility", "PatientStatus.Name"}),
#"Added Custom7" = Table.AddColumn(#"Removed Other Columns2", "Severity per day", each if[#"SeverityDesc."] = "Severe" then 1 else 0),
#"Changed Type6" = Table.TransformColumnTypes(#"Added Custom7",{{"Severity per day", Int64.Type}})
in
#"Changed Type6"
Any ideas?
