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

[huonghill]

Hi, 

I'm having the error mentioned in the post title. I do see null values in some columns (both number and text). However, when I replace null values with either 0 or "blank" or "none", PBI doesn't let me apply the change. The same error pops up

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

Please let me know how I fix this. Your help is much appreicated.

[v-yingjl]

Hi @huonghill ,

Which data source you are using? I have tested it in my environment and it seems work fine when I replace values. Could you please share the power query code in advanced editor for further discussion?

Best Regards,

Yingjie Li

[huonghill]

Hi @v-yingjl , the data source is SharePoint 😑

[v-yingjl]

Hi @huonghill ,

Try like this in power query:

= Table.ReplaceValue(
    #"Changed Type","null","blank",Replacer.ReplaceValue,{"Column1","text"}
)

Remember that "null" and null are different in power query.

Best Regards,
Yingjie Li

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

[huonghill · Accepted solution]

Hi @v-yingjl , 

Thank you so much for your help! After a long search, I figured out that one column has a null values that causing a blank row in my table. It was a quick fix that I excluded null values in the transform data view. Your help is much appreciated

[amitchandak]

@huonghill , refer if this can help

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

https://stackoverflow.com/questions/56123282/ole-db-or-odbc-error-we-cannot-convert-the-value-null-t...