Cannot convert type null to type logical

Question

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.&nbsp;&nbsp;This is the advanced editor code for the query:&nbsp;letSource = 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] &lt;&gt; "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] &lt;&gt; "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 nullthen [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&lt;Other fix" = Table.AddColumn(#"Changed Type5", "DischargeDelete", each if [OutcomeDate.1] = null then 0 else if [DatesBetween] &gt;= [OutcomeDate.1] then 1 else 0),#"Added Custom9" = Table.AddColumn(#"Disch&lt;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"&nbsp;Any ideas?&nbsp;

v-xuding-msft · Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
I check the steps and don't find that you change the type to logical. So which step do you encounter the issue?&nbsp; Is the step #"Added Custom7"? You could use remove errors to test firstly.
&nbsp;

&nbsp;

Forum Discussion

Related content

Logic problem?

[Expression.Error] cannot convert null value to Logical

FX Conversion Logic

We cannot convert the value null to type Logical error

Help Converting Tableau Date logic to PowerBI