We cannot convert the value null to type Logical in Service

[helgerg]

I have a report reading from Azure SQL and Data Lake Store. When updating in Power BI Desktop it works fine, but in Service it says

"We cannot convert the value null to type Logical" and then only the table name. I have gone through all columns and cannot find null values, so I'm struggeling to see where the error could be. Any tips on where to look? 

[Anonymous]

Hi @helgerg,

Is any transform operation in query editor, such as custom function, merge/combine query operations in your report?

In addition, you can also take a look at below link about known issue to refresh azure data lake store, you can try to remove azure data lake data source and publish your report to confirm if your issue related to azure data lake source.

Azure Data Lake refresh issue

Regards,

Xiaoxin Sheng

[helgerg]

I have the following expression that caused the error (it worked when I removed it):

OutAndContinued = IF(AND(MoverChannelAudit[EventType]="Continued Customer";MoverChannelAudit[EventTypeFilter]="MoverOutflow");"Yes";"No")

As both EventType and EventTypeFilter has values for all columns I don't understand why this expression returns null

[Anonymous]

Hi @helgerg,

Maybe you can try to use iferror to handling error records and replace them as 'No' result.

IFERROR Function (DAX)

Regards,

Xiaoxin Sheng

[helgerg]

I have now tried to remove all computed columns, all measures, and all type conversions or other steps in the query. It still fails... 

It works if I remove the data lake table... 

[Anonymous]

HI @helgerg,

I think it might be a issue with azure data lake service, I'd like to suggest you open a support ticket for this scenario to get more support from power bi team.

submit a support ticket

Regards,

Xiaoxin Sheng

[CasperLehmann · Accepted solution]

When you use the Azure Data Lake connector Power BI creates your query for you. The resulting query, running through DataLake.Contents is then supplied with default parameters that (at the time of writing) break the evaluation once uploaded to the Power BI service. The problem is the second param, the empty record []. Exchange it for a null, or simply delete it.

Bad:

DataLake.Contents("adl://adlstest01.azuredatalakestore.net/data/examples/entries.csv", [])

Good:

DataLake.Contents("adl://adlstest01.azuredatalakestore.net/data/examples/entries.csv", null)

At least this worked for me.

[JulianPayne_i4]

When trying to connect to AZDL with SSAS Azure in Visual Studio, I keep getting this error:

Details: "We cannot convert the value null to type Logical."

I do not get this error in Power BI using the same connection string.

connection string: adl://<mydata>.azuredatalakestore.net

Any thoughts?

In VS, I am not able to open a blank query to write this correct version of the connection string, as far as I am aware.

[helgerg]

thanks, it did the trick 🙂