Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jerrywalk1
Regular Visitor

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

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,

9 REPLIES 9
NickA01
Resolver III
Resolver III

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. 

NickA01_0-1626696394630.png

@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

watkinnc
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

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"

Anonymous
Not applicable

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.

Anonymous
Not applicable

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.

kbachova
Advocate II
Advocate II

Hi, I am having a similar issue...did you find any solution to this?

 

Thanks!

Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.


 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.