Power Query - We cannot convert the value null to type Number.

[Lachlanpap]

Hello, I keep getting the below error when loading data into Power Bi despite having now issues with the data in power query. 

OLE DB or ODBC error: [Expression.Error] We cannot convert the value null to type Number..

No matter I try to do I keep getting this error message. Can someone please help me. Thanks

[BA_Pete]

Hi @Lachlanpap ,

My first guess based on the information provided is that you're trying to sum columns using [Col1] + [Col2] syntax. Power query will give errors using this syntax when null values are encountered.

If this is the case then, in Power Query, you can select any columns where you're applying this calculation, go to the Home tab > Keep Rows (dropdown) > Keep Errors. This will filter your table to the offending rows, allowing you to understand and remedy the issue.

If this is the issue, but you can't remove/replace null values to avoid the errors, then you should use the following sum syntax instead, which will handle null values:

List.Sum({[Col1, [Col2], [Col3]})

If neither of these get you to a solution, you'll need to post your M code here in a code window ( </> button above) to allow a proper review of where the issue may be occurring. Please make sure to anonymise any server/DB details etc. by replacing with XXX or similar.

Pete

[Lachlanpap]

Thanks for your response Pete.

The 'RequiredRows' column is returning 0 when an arrival and departure time is less than 1 hour. I created a conditional column 'RequiredRows1' that if 'RequiredRows' = 0 then return 1. This was done so I could then enter the below M code which adds a row for each hour between arrival and departure time. If I didn't do this the 'Custom' column would return a null for any row less than 1 hour. 

I hope this makes sense. Thanks

[Anonymous]

Hi @Lachlanpap ,

DIdi the solution BA_Pete  offered help you slove the problem, if it helps you can consider to accept it as a solution so that more user can refer to, or if you have problems you can provide some information so that can provide more suggestion for you.

Best Regards!

Yolo Zhu

[BA_Pete]

Hi @Lachlanpap ,

How have you identified this part of your code as the problem? Is one of these steps showing an error in the Query Builder when you select it? If so, exactly which step is giving you the error? If not, then you may be focusing on the wrong part of the issue and creating an XY Problem.

I can't immediately see any issues with the code snippet you've provided. However, I can see that your Required Rows columns both appear to be untyped, which could quite easily be causing the issue you describe, and/or preventing you from being able to identify it within the Query Builder. Make sure that both of these columns (and ALL other columns) are changed to an appropriate data type (probably Whole Number in this case) before you try to apply your changes to the model:

Again, if this doesn't fix the issue, or at least highlight it within the Query Builder, you'll need to provide ALL of your M code (anonymised) here. Without seeing everything that's happening in the query I just can't see where this issue may be occurring.

Pete