CosmosDB - We cannot convert a value of type Record to type Table

[andyinv]

Hi all

Got a heap of data in CosmosDB, and it looks like one of the documents has some bad code in it. When I try to refresh, am getting

OLE DB or ODBC error: [Expression.Error] We cannot convert a value of type Record to type Table.

However, it won't give me any indication of which document the issue is in. So in effect, my entire database is un-refreshable. Have enabled tracing, but not seeing that error in there. Is there no way when PBI finds an issue for it to actually tell me WHERE the issue is?

To be clear - this isn't an import-steps issue. There is a corrupt record in my CosmosDB, an invalid JSON file, that while it allowed me to post it, PBI won't retrieve it and it junks the refresh as a result. I need PBI to tell me which document it is, somehow.

Thanks

[amitchandak]

You might have to go to edit query and check the M code. Refer similar one

https://community.powerbi.com/t5/Power-Query/Expression-Error-We-cannot-convert-the-value-to-type-Li...

https://community.powerbi.com/t5/Desktop/Expression-Error-We-cannot-convert-a-value-of-type-Record-t...

[andyinv]

Maybe I'm missing something - how will the m-code help me identify an individual document that has issues? Can you provide some relevant link that might help me in that, thanks?

[dax]

Hi @andyinv , 

I am not sure which part cause this problem, what step did you create when you get this error. You could try to check this in your M code(Eidt Queries->applied steps) to see which step have error information. And you could try to upload these steps or M code and your expected output, then we could try to find the problem.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

[andyinv]

There's nothing wrong with my import steps - this is a data error. This has only happened because a corrupt record was inserted to CosmosDB and PBI isn't happy about it when I go to refresh the dataset and pull in the new records.

What I've found is that CosmosDB will quite happily accept a faulty JSON document to store, but even its own built-in data explorer will then refuse to retrieve the field afterwards.

So, how do I get PBI to tell me which CosmosDB document is throwing the problem? I was able to work manually through the dataset in the CosmosDB interface a little and find some, but this isn't a very workable solution. There must be a way for PBI to say "well, I loaded 72,000 records, and 72,001 gave me a problem. It has this ID"

Surely?

[andyinv]

Anyone?