Tip #807: Data refresh in Power BI using old endpoint fails

Today’s tip is from our almost regular Matt Johnson.

I was recently tasked with creating a Power BI dashboard based directly off the CRM (sorry, I mean Dynamics 365) data. We were in a bit of a rush and even though the performance isn’t great we only needed a few charts and it was only a POC. Using Power BI Desktop you can add a datasource and select Dynamics 365 form the list.

Power BI Get Data

Then it prompts you for the OData service (notice the format it is asking for)

Power BI URL Prompt

So you fill in your org as prompted, connect up as a user and away you go. You can build you dashboards/reports/datasets and refresh them to your hearts content.

Then when publish your report to the online Power BI service, it allows you to do it no problem. However, if you then try and refresh your dataset, you get an error,

Your data source can’t be refreshed because the credentials are invalid. Please update your credentials and try again.

Power BI Invalid Credentials

OK you might say, I’ll re-enter my credentials. So you change the Authentication Method from the default Anonymous to OAuth2 but you still get the error.

Power BI failed to update

After much searching I came across this TechNet article where it tells you that you must use the new web api in the format https://foo.api.crm.dynamics.com/api/data/v8.1 to connect to your org in PowerBI Desktop. Even though it prompts you for the older SOAP endpoints in the format https://bar.crm4.dynamics.com/XRMServices/2011/OrganizationData.svc.

Using the new web api allows you to get at all your data and publish the reports out to PowerBI online and then you can successfully connect and refresh your data.

Power BI Successfull Refresh
Power BI Successfull Connect using OAuth2

Thanks, Matt for the tip, as you said yourself – maybe it will save someone a lot of messing about!

2 thoughts on “Tip #807: Data refresh in Power BI using old endpoint fails

  1. Bill says:

    Connecting to this endpoint doesn’t give me any service data or project service data (Cases, Activities, Projects, etc). How would I go about getting that data?

    • Bill says:

      Found it. The endpoint is fine – the name of the entities just took a bit more figuring out. E.g. Case (CRM display name) = incident (Entity name).

Leave a Reply

Your email address will not be published. Required fields are marked *