Ir al contenido

Cache Clearing (Excel & Power BI)

Operating protocol to resolve metadata conflicts and cached schemas following the deployment of new APIs in Business Central.

0views

In our continuous integration/continuous deployment (CI/CD) operations, it is common for us to modify APIs in AL language to add new columns or rename existing ones (e.g., moving from numDocumento to Num_Documento).

Even if the deployment in Business Central is successful, Microsoft client tools (Excel and Power BI Desktop) use an extremely aggressive metadata caching system to save bandwidth. This causes them to attempt to read the API based on an outdated "snapshot" of the schema, resulting in complete data ingestion failures.

The unmistakable symptom of this conflict is the following error in Power Query:

Detalles: "OData: The property 'numDocumento' does not exist on type 'Microsoft.NAV.comercialAlbaranCompra'. Make sure to only use property names that are defined by the type or mark the type as open type."

DevSecOps Diagnosis: The source database has changed, but the local client (Excel/Power BI) refuses to download the new OData $metadata document. It continues to request the old column and the server rejects the request.

When consuming our analytical APIs directly from Excel (via Power Query), we must purge two different caches to force the schema update:

  1. In the ribbon, go to the Data tab.
  2. Click Get Data -> Data Source Settings.
  3. Find the path to our Business Central API in the list of global sources or the current workbook.
  4. Click Clear Permissions.
  1. Open the Power Query Editor (by double-clicking our query in the right panel).
  2. Go to File -> Options and settings -> Query Options.
  3. In the left panel, under the 'GLOBAL' section, select Data Load.
  4. Click the Clear Cache button.
  5. Finally, in the Power Query Editor itself, click the Refresh Preview button. The system will re-authenticate and read the new columns correctly.

Behavior in Power BI is identical to Excel, but the options are distributed slightly differently.

  1. Go to File -> Options and settings -> Options.
  2. In the left sidebar menu, under the “GLOBAL” heading, select Data Load.
  3. In the “Data Cache Options” section, click on Clear Cache.
  4. (Optional) If we use Q&A, we also click on “Clear Q&A cache”.
  5. Close the options window.
  6. In the main view (or in Power Query), click Refresh. Since no local cache is found, Power BI will perform a clean HTTP call to download the new JSON schema from our API, resolving the error instantly.
  • ✓ Permission Debugging: Deletion of corrupt global credentials following reorganizations or Entra ID UPN changes.
  • ✓ Schema Update: Purging local data cache in Power Query to reload the ERP $metadata document.
  • ✓ Final Validation: Verification of imported columns in the Power Query view to ensure they match the current AL definition.