Cosmos db - Power BI will not accept COUNT() , GROUP BY key works in the SQL query window

[Anonymous]

Hi all,

I was in the process of creating a very simple Power BI report using data which resides in Azure Cosmos DB , with the following queries.

1. SELECT COUNT(1) from c
2. SELECT COUNT(1) as groupcount,c.OnboardingStatus FROM c group by c.OnboardingStatus

Cosmos DB Data explorer on Azure portal has no problems

However, Power BI desktop will throw an error.

Details: "The field 'errors' of the record wasn't found."

Any suggestions?

Thanks,

Saurabh

[v-easonf-msft]

Hi , @Anonymous 

The problem appears to be with ODBC driver. 

Please refer to the  below  related  post and article here :

https://social.msdn.microsoft.com/Forums/en-US/c361dd5c-7d3f-404f-98c0-1d094ff83505/powerbi-getdata-from-azure-cosmosdb?forum=azurecosmosdb

https://community.powerbi.com/t5/Power-Query/Cosmos-Table-DB-Expression-Error-The-field-errors-of-the-record/td-p/560329

https://docs.microsoft.com/en-us/azure/cosmos-db/odbc-driver

If the issue is still not resolved,  sorry for that  I can't fix it on my side currently .Please kindly raise it to the support team for better advice, thanks a lot!
https://powerbi.microsoft.com/en-us/support/  

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi ,

Many thanks for looking into this. 

If I create a report using the ODBC connection and upload to Power BI server, will the server be able to make a connection to Cosmos DB when I click on the Refresh button of the Data Set when online?

Thanks,

Saurabh

[Anonymous]

Further to your response, I went ahead and configured ODBC driver for Cosmos DB. Created a report and published to Power BI online.

Good - This solved my querying issues. Thank you.

Challenge -  While refreshing the dataset online, I received the error "Refresh failed due to gateway configuration issues."

Question  - It is my understanding that the ODBC approach is useful if I am trying to connect to my data server which is on premises. Is this of any value if I my data resides on Azure (Cosmos DB)?

Thanks,

Sau