Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am getting the error "we cannot convert the value null to type logical..". The problem is that it tells me what table when trying to load but it does not tell me what column nor does it create an error query. I cannot find the null value nor does it give me which expression it is erroring. Everything has been working fine until just recently. How can I find exactly where it the error is from?
Thanks,
Hi All, hopefully this has already been resolved but if not hopefully this helps:
1: Source is an excel workbook- A blank cell in Excel and a cell with a space in it is not a null.
Easiest way to check IMHO is as per below.
@Anonymous same can be said for SQL- If you are querying SQL, try adding to your where clause
Where <ColumnName> IS NOT NULL
This will remove the rows causing this issus.
If you need to include, try adding in a case statement to inspect the column
Case when <ColumnName> Is NULL then 1 else 0 end as IsnullValue
One of your functions is asking for a true or false value, and is instead being given null. You need to click on the applied steps one by one until you see which step is causing the error. If you would post the M, I'm sure I could spot the issue.
--Nate
Hi @watkinnc 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"
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.
I have the same error from today. I haven't changed nothing, and I tried to search the null values by a direct sql query (outside PowerBI): nothing is null! Please tell me if you solved this problem, thanks.
Hi, I am having a similar issue...did you find any solution to this?
Thanks!
In the Query Editor, logical columns should show up as having a red X and green checkmark in their column header. Perhaps you can track it down that way. The other way would be to filter out nulls on each column until you find the right one.
So, i looked at the columns and there were no red x or green checkmarks. I went thru each column and had it remove null values. Nothing changed. I keep getting Value = and Type = Type with the error we cannot convert value to type logic. This is frustrating as I know its in the current table but nothing tells me where.
sorry i have been out on vacation. I will try the solution today.
@Greg_Deckler wrote:In the Query Editor, logical columns should show up as having a red X and green checkmark in their column header. Perhaps you can track it down that way. The other way would be to filter out nulls on each column until you find the right one.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |