Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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?
Hi @Anonymous ,
I check the steps and don't find that you change the type to logical. So which step do you encounter the issue? Is the step #"Added Custom7"? You could use remove errors to test firstly.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
86 | |
85 | |
66 | |
49 |
User | Count |
---|---|
140 | |
113 | |
104 | |
64 | |
60 |