we cannot convert the value null to type logical...

Thank you so much.

Yesterday evening I found that it would be a boolean column that I did long time ago, but I didn't find the cause... while today Yes! There is another column that gives null and I didn't think that it could, so now I know how to manage it.

Thank you very much.

Hi Anonymous I am having the problem as above and whilst I have identified the comumns which may return a "null" value I can't decipher which in particular is causing the issue. Would you mind checking the M for me please? Posted below:

let
Source = SharePoint.Files("https://hants.sharepoint.com/sites/HROps7104/", [ApiVersion = 15]),
#"WFP Workforce Data xlsx_https://hants sharepoint com/sites/HROps7104/Shared Documents/PDR data/" = Source{[Name="WFP Workforce Data.xlsx",#"Folder Path"="https://hants.sharepoint.com/sites/HROps7104/Shared Documents/PDR data/"]}[Content],
#"Imported Excel" = Excel.Workbook(#"WFP Workforce Data xlsx_https://hants sharepoint com/sites/HROps7104/Shared Documents/PDR data/"),
#"HC Staff Data_Sheet" = #"Imported Excel"{[Item="HC Staff Data",Kind="Sheet"]}[Data],
#"Promoted Headers1" = Table.PromoteHeaders(#"HC Staff Data_Sheet", [PromoteAllScalars=true]),
#"Rounded Off" = Table.TransformColumns(#"Promoted Headers1",{{"Length of Service at Report Date", each Number.Round(_, 2), type number}}),
#"Added Conditional Column" = Table.AddColumn(#"Rounded Off", "Custom", each if Text.Contains([Position name], "Student") then "No" else null),
#"Filtered Rows6" = Table.SelectRows(#"Added Conditional Column", each true),
#"Added Conditional Column1" = Table.AddColumn(#"Filtered Rows6", "Custom.1", each if [Length of Service at Report Date] < 1 then "No" else null),
#"Filtered Rows" = Table.SelectRows(#"Added Conditional Column1", each ([Custom] = null) and ([Custom.1] = null)),
#"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"Personnel no."}, #"SAP data - Split 1", {"Employee"}, "SAP data - Split 1", JoinKind.LeftOuter),
#"Expanded SAP data - Split 1" = Table.ExpandTableColumn(#"Merged Queries", "SAP data - Split 1", {"PDR due date"}, {"PDR due date"}),
#"Merged Queries1" = Table.NestedJoin(#"Expanded SAP data - Split 1", {"Personnel no."}, #"SAP data - Split 2", {"Employee"}, "SAP data - Split 2", JoinKind.LeftOuter),
#"Expanded SAP data - Split 2" = Table.ExpandTableColumn(#"Merged Queries1", "SAP data - Split 2", {"PDR complete date", "Revised rating"}, {"PDR complete date", "Revised rating"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded SAP data - Split 2",{{"PDR complete date", "SAP PDR complete date"}, {"Revised rating", "SAP PDR rating"}}),
#"Filtered Rows5" = Table.SelectRows(#"Renamed Columns", each true),
#"Merged Queries2" = Table.NestedJoin(#"Filtered Rows5", {"Personnel no."}, #"PDR log data", {"Personnel Number"}, "PDR log data", JoinKind.LeftOuter),
#"Expanded PDR log data" = Table.ExpandTableColumn(#"Merged Queries2", "PDR log data", {"Date PDR #(lf)completed", "Rating"}, {"Date PDR #(lf)completed", "Rating"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded PDR log data",{{"Date PDR #(lf)completed", "Log PDR #(lf)complete date"}, {"Rating", "Log PDR rating"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Continuous Service Date", type date}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type", each true),
#"Renamed Columns2" = Table.RenameColumns(#"Filtered Rows1",{{"Level 1", "Command"}, {"Category", "Staff / Officer"}}),
#"Filtered Rows2" = Table.SelectRows(#"Renamed Columns2", each true),
#"Replaced Value" = Table.ReplaceValue(#"Filtered Rows2",null,"Outstanding",Replacer.ReplaceValue,{"SAP PDR rating"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"Outstanding",Replacer.ReplaceValue,{"Log PDR rating"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","PCSO","Staff",Replacer.ReplaceText,{"Staff / Officer"}),
#"Filtered Rows3" = Table.SelectRows(#"Replaced Value2", each true),
#"Added Conditional Column2" = Table.AddColumn(#"Filtered Rows3", "ACC Command", each if Text.StartsWith([Name of organizational unit], "Logistics") then "ACC" else if Text.StartsWith([Name of organizational unit], "Health") then "ACC" else if Text.StartsWith([Name of organizational unit], "Driver Training") then "ACC" else if Text.StartsWith([Name of organizational unit], "Personal Safety Team") then "ACC" else if Text.StartsWith([Name of organizational unit], "Training Administration") then "ACC" else if Text.StartsWith([Name of organizational unit], "Crime Skills") then "ACC" else if Text.StartsWith([Name of organizational unit], "Development Team") then "ACC" else if Text.StartsWith([Name of organizational unit], "Learning and Professional") then "ACC" else if Text.StartsWith([Name of organizational unit], "First Aid Training") then "ACC" else if Text.StartsWith([Name of organizational unit], "Digital Policing") then "ACC" else if Text.StartsWith([Name of organizational unit], "Operational and Initial") then "ACC" else if Text.StartsWith([Name of organizational unit], "PDRO") then "ACC" else if Text.StartsWith([Name of organizational unit], "Communities") then "ACC" else if Text.StartsWith([Name of organizational unit], "Investigation Accreditation Team") then "ACC" else if Text.StartsWith([Name of organizational unit], "ACE Implementation") then "ACC" else if Text.StartsWith([Name of organizational unit], "ICTU") then "ACC" else if Text.StartsWith([Name of organizational unit], "Serious Crime Review") then "ACC" else null),
#"Merged Queries3" = Table.NestedJoin(#"Added Conditional Column2", {"Personnel no."}, #"HC Ethnicity Data", {"Personnel number"}, "Query1", JoinKind.LeftOuter),
#"Expanded Query1" = Table.ExpandTableColumn(#"Merged Queries3", "Query1", {"PA - Ethnic Origin"}, {"PA - Ethnic Origin"}),
#"Removed Duplicates" = Table.Distinct(#"Expanded Query1", {"Personnel no."}),
#"Added Conditional Column3" = Table.AddColumn(#"Removed Duplicates", "Ethnicity grouped", each if Text.StartsWith([#"PA - Ethnic Origin"], "White") then "Non BAME" else if [#"PA - Ethnic Origin"] = "No data provided" then "No data provided" else if [#"PA - Ethnic Origin"] = "Prefer not to say" then "Prefer not to say" else if [#"PA - Ethnic Origin"] = null then "No data provided" else "BAME"),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Added Conditional Column3", {{"Ethnicity grouped", "No data provided"}}),
#"Renamed Columns3" = Table.RenameColumns(#"Replaced Errors",{{"Command", "Command full"}, {"Name of superior (OM)", "Line Manager"}}),
#"Filtered Rows4" = Table.SelectRows(#"Renamed Columns3", each ([PDR due date] <> null))
in
#"Filtered Rows4"