O365 Business Central ODATA connection uses report doesn't recognise creditioal in Power BI Service

[JJHA]

Hi All,
I have power bi file getting data from O365 Business Central using OData. In Power BI desktop it is using fine, when I published It in PBI Service it worked but for scheduled refresh it gives me error in Data Source Credentials. I am using personal Gateway which is running fine. I think I am missing something in gateway configration. 
Pls help. I have attached the error message I am getting.

Thanks, Jag 

[v-xulin-mstf · Accepted solution]

Hi @JJHA

Mashups between a cloud source and an on-premises source, in a single query, fail to refresh in the gateway (personal mode) if one of the sources is using OAuth for authentication. An example of this issue is a mashup between CRM Online and a local SQL Server instance. The mashup fails because CRM Online requires OAuth.

This error is a known issue, and it's being looked at.

To work around the problem, have a separate query for the cloud source and the on-premises source. Then, use a merge or append query to combine them.

For more information, please refer: https://docs.microsoft.com/en-us/power-bi/connect-data/service-admin-troubleshooting-power-bi-person...

If you still have some question, please don't hesitate to let me known.‌‌

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[JJHA]

Hello Guys,
I found the solution, it was due to I wasn't using right Business Central Reporting APIs URL. It has to be base url without key and exclude report name from URL then it should work. Like shown in below URL exclude colored text from BC APIs  URL when connecting in power bi as a OData feed data source. Use auth2 option for authentication in power bi service for auto refresh.

https://api.businesscentral.dynamics.com/v2.0/471e5b26-b587-4e65-b08f-fc766e8892de/Production/ODataV4/Company(xxxxxxxx)/"Report Name"

Regards,
Jag