We cannot convert the value 0 to type Text.. Refreshes in Desktop but not in service

[grahamotter]

Hi folks,

I'm having real difficulty finding an answer for this one and I can usually find an answer, or something close enough for me to devise something from the web, somewhere

I have a Power BI report which is connected to an SQL database. The report refreshes fine in the desktop version but will not refresh in the web service. I get the following error message:

Data source error:

We cannot convert the value 0 to type Text.. The exception was raised by the IDbCommand interface. Table: Orders Count.

I have searched for days and can't find anything which actually helps me solve this issue. It would be so much easier if Power BI error message actually mentioned which field the issue lies in but sadly not. Something which came close to my query is here 

We-cannot-convert-the-value-null-to-type-Logical-in-Service/ 

Something I did notice is that there is a double dot after the word Text in the error message. I'm not sure if this is a red herring though as I've searched for this along my 2 days of trying to fix this issue but did not find a value of '..' the the table Orders Count

Thanks in advance for any help you can offer

[grahamotter]

Still struggling with this as the error message isn't very helpful.

I'm getting to the point of attempting to rebuild the report but this will take a 2 to 3 days to do so I'm reluctant to go down this route in a hope this may fix it

Any more suggestions would be gratefully received

[grahamotter]

Can anyone offer any more help with this?

I've spent 2 days rebuilding the report after stripping the table which was causing the error of the code and adding back one line at a time. After adding each line I re-published and manually refreshed in the web service to test whether it was that line which was causing the issue. I thought I had found the issue and so changed the code to a different approach to achieve the same thing and then re-published and refreshed in the web service. This seemed to work, i.e. the manual refresh didn't fail with the error message i was getting.

I then continued to spend 2 days completely rebuilding the report only to find when I re-published and refreshed in the web service I was back to square one with the same error message (just now 2 extra days spent on this)

Is there a way to raise a support ticket with microsoft who can help me better understand this error message and be able to come to a solution soon?

[v-lili6-msft]

hi, @grahamotter 

Try to remove the [] directly in the M code as below:

Before:

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

After:

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

Regards,

Lin

[grahamotter]

Hi there,

Thanks for the reply but there's no [] in any of the code.

The table which is causing an issue, according to the web service, is a copy of another table (the main table). Neither of these have a [] listed anywhere in the code

My data is coming directly from a view I created in Azure SQL