By: Nicholas Jackson, Senior Microsoft Dynamics Consultant
A common issue that many of our clients run into when doing Business Central reporting, either with Power BI or Power Query via Excel, is the following error:
Details: The odata.context url ‘’ is invalid.
First, let’s see what causes this error, and secondly, review the workaround.
Cause of Error
The root cause of this error is related to how Power Query (either from Power BI or inside Excel) caches its data. Essentially, what is happening is that a new OData URL endpoint is being added after Power Query has already reached out to the Business Central tenant.
The first part of any OData request that Power Query makes is to get data from an endpoint called $metadata. This endpoint returns a list of all valid OData endpoints for this instance. Power Query saves a copy of this list for future reference to reduce the number of extra calls made to the endpoint. This is a convenient feature, but it causes issues when we have the ability to change the OData endpoints on the fly.
Most OData implementations are static; endpoints rarely update, and if they are, it’s part of a major update. However, in Business Central and other related applications, we have the ability to create new OData endpoints on the fly (such as using the Web Services page in BC). If we create a new OData endpoint after Power Query has already retrieved a list of valid OData endpoints, we will receive this error because Power Query thinks it has a good master list of all endpoints and the new one that was provided is not valid, even though it is.
To resolve this, we must remove the cached list of endpoints that Power Query is using.
Workaround – Power BI
Here are the steps for the workaround for Power BI Desktop:
1. Save all your open Power BI reports. These steps include a clean restart of Power BI.
2. In any of your Power BI reports, navigate to File > Options and settings > Options.
3. On the Global Data Load section, click Clear Cache.
This should change the cache size currently used to 0 bytes.
4. Hit OK to close the pane.
5. Restart all instances of Power BI Desktop currently open.
6. Reopen your report.
7. Add your OData URL data source as normal.
The clearing of this cache will force Power Query to fetch a new list of valid OData endpoints allowing you to use your newly created endpoint.
Workaround – Excel
The steps for the workaround for Excel are very similar, but the setting is accessed slightly differently.
To perform the workaround:
1. Save all your open Excel Workbooks. These steps require a full restart of Excel.
2. In any Excel Workbook, go to Data > Get Data > Query Options.
3. Go to the Data Load section and click Clear Cache.
This will set the currently used cache size to 0 bytes.
4. Select OK.
5. Close all Excel Workbooks.
6. Open an Excel workbook and add the OData endpoint as normal.
While this could be considered a bug from Microsoft, after discussing with Microsoft’s development team, they have claimed it to not be a bug. Fortunately, it has a simple workaround. To further help prevent running into this error, consider adding all your web services at the beginning of a report building session to reduce the times this error is encountered.
Expertise and hands-on experience, as demonstrated in this post, are qualities exclusive to experienced Microsoft Partners who consistently utilize the products and guide their clients in resolving business challenges with Microsoft solutions. Contact Opal today to see first-hand how our experienced team of knowledgeable staff can help your organization with Microsoft Dynamics and Power BI!
