Ir al contenido

Advanced M Code (Power Query)

Deterministic extraction scripts and patterns in M language for robust consumption of OData V4 APIs.

0views

To govern data extraction from our Native APIs (ELT) to the Power BI semantic model, we avoid the graphical interface and write the Power Query code (Language M) directly in the Advanced Editor.

This brings us closer to deterministic and controllable development, where we govern every parameter of the underlying HTTP request.

The core of our ingestion uses the OData.Feed function. Instead of relying on auto-generation, we standardize this base snippet for all extractions from Business Central:

let
// 1. Definición de la URL absoluta apuntando a nuestro Endpoint AL (EntitySetName)
UrlBase = "https://api.businesscentral.dynamics.com/v2.0/a1b2c3d4-e5f6-7a8b-9c0d-1e2f3a4b5c6d/Sandbox-Dev/api/portelcore/api_portelcore_1/v1.0/companies('PORTELCORE%20S.L.U')/movimientos",
// 2. Invocación de la API con inyección de protecciones de red
Origen = OData.Feed(UrlBase, null, [
Implementation="2.0",
Timeout=#duration(0, 0, 10, 0)
])
in
Origen
  • Implementation="2.0": Forces Power Query to use the most modern version of the OData engine. It optimizes JSON reading at a binary level and metadata parsing.
  • Timeout=#duration(Días, Horas, Minutos, Segundos): Our insurance against system lockups. While our API in AL is optimized to take ~30 seconds, we inject a strict 10-minute limit (0, 0, 10, 0). If Microsoft's ERP suffers a severe degradation, Power BI will abort the load without keeping processes in memory indefinitely.

These are the exact scripts we have integrated into our main Datasets to feed the business intelligence model.

let
UrlBase = "https://api.businesscentral.dynamics.com/v2.0/a1b2c3d4-e5f6-7a8b-9c0d-1e2f3a4b5c6d/Sandbox-Dev/api/portelcore/api_portelcore_1/v1.0/companies('PORTELCORE%20S.L.U')/movimientos",
Origen = OData.Feed(UrlBase, null, [Implementation="2.0", ODataVersion=4, Timeout=#duration(0, 0, 10, 0)])
in
Origen
let
UrlBase = "https://api.businesscentral.dynamics.com/v2.0/a1b2c3d4-e5f6-7a8b-9c0d-1e2f3a4b5c6d/Sandbox-Dev/api/portelcore/api_portelcore_1/v1.0/companies('PORTELCORE%20S.L.U')/lineasCompra",
Origen = OData.Feed(UrlBase, null, [Implementation="2.0", ODataVersion=4, Timeout=#duration(0, 0, 10, 0)])
in
Origen
let
UrlBase = "https://api.businesscentral.dynamics.com/v2.0/a1b2c3d4-e5f6-7a8b-9c0d-1e2f3a4b5c6d/Sandbox-Dev/api/portelcore/api_portelcore_1/v1.0/companies('PORTELCORE%20S.L.U')/lineasVenta",
Origen = OData.Feed(UrlBase, null, [Implementation="2.0", ODataVersion=4, Timeout=#duration(0, 0, 10, 0)])
in
Origen

Since our architecture is based on Pushdown Computation (ELT), the fundamental rule of our M scripts is to keep local computational load at zero.

  1. No filters (WHERE) in M: All row exclusions (e.g., omitting certain warehouses or dates) were already executed in the Business Central SQL engine via the DataItemTableFilter property.
  2. No column removal in M: Only columns strictly projected in the endpoint travel through the HTTP network. We must not fetch 100 columns to delete 90 in Power Query.
  3. The only thing we perform locally is the final data typing (Type Casting) via Table.TransformColumnTypes, ensuring that the DAX model receives immutable data (e.g., Int64.Type, type date, type number).
  • ✓ M Implementation: Explicit use of the Implementation="2.0" parameter in all queries for accelerated data loading.
  • ✓ Latency Control: Preventive injection of a 10-minute Timeout to protect the stability of the refresh queue in Power BI Service.
  • ✓ ELT Optimization: Zero local filters applied at the Power Query level, keeping all heavy data logic in the SQL backend.