Cannot Convert Type Null to Logical

[smather]

I'm about ready to throw my whole laptop out of the window, so any help would be really appreciated. 

I'm currently re-doing a dashboard we currently have to optimize the refresh and data load. I'm all but mirroring the previous dashboard in the queries that I'm keeping, and that dashboard is still live, working and refreshes without a problem. 

I've having an issue with: Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [Expression.Error] We cannot convert the value null to type Logical.. OLE DB or ODBC error: Exception from HRESULT: 0x80040E4E.'.

I've heard it can sometimes be caused by a data mismatch when you append queries (I have two/three tables that do this) but when I refreshed it (with the table removed from the refresh that I think is causing it, not the appended queries one(s)), it loaded fine. If I add back the two tables that I think are causing the issue (one is reference table from the other) I get smacked with the above error. 

There is only one custom col that refers to "null", and I even changed it from = null to is null to try and circumvent the issue, but no joy. The code that refs null is: 

= Table.AddColumn(#"Changed Type6", "Date 2", each if [PO Recieved] is null then [estimatedclosedate] else [PO Recieved])

If I look at the col quaility it's 99% Valid, 0% error and <1% Empty for the date col. Both Estimated Date and PO Rec Date have nulls in them. 

No other table makes ref to any nulls, nor are there any other tables with custom cols that ref any cols with null values, so it has to be this table that has the issue, but I can't figure it out for the life of me. Please help.

[Anonymous]

Hi  @smather ,

1. There may be some errors in the line that you cannot see. Errors are different from null, you can add a step to remove errors on each table

2. Is it possible to replace the null inside with Number or Text.

https://community.powerbi.com/t5/Power-Query/We-cannot-convert-the-value-null-to-type-Number/m-p/205...

https://stackoverflow.com/questions/35904951/power-query-m-we-cannot-convert-the-value-null-to-type-...

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@smather .

this should be like

= Table.AddColumn(#"Changed Type6", "Date 2", each if [PO Recieved]= null then [estimatedclosedate] else [PO Recieved])

Also, check for a boolean column. If you have empty/null value there, it might be giving an error there

[smather]

Hi @amitchandak  I changed it back to = null, but the error still occurred 😕 I did have a couple of cols that are true/false that had null values, so I replaced the null with false, and still no joy.  Any other ideas?