Advanced M Code (Power Query)
Deterministic extraction scripts and patterns in M language for robust consumption of OData V4 APIs.
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.
1. The Base OData Extraction Pattern
Sección titulada «1. El Patrón Base de Extracción OData»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 OrigenExplanation of Critical Parameters (DevSecOps)
Sección titulada «Explicación de los Parámetros Críticos (DevSecOps)»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.
2. Ingestions Deployed in Production
Sección titulada «2. Ingestas Desplegadas en Producción»These are the exact scripts we have integrated into our main Datasets to feed the business intelligence model.
2.1. Product Movements (Logistical Facts)
Sección titulada «2.1. Movimientos de Producto (Hechos Logísticos)»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 Origen2.2. Purchase Lines
Sección titulada «2.2. Líneas de Compra»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 Origen2.3. Sales Lines
Sección titulada «2.3. Líneas de Venta»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 Origen3. Post-Load Transformation Considerations
Sección titulada «3. Consideraciones de Transformación Post-Carga»Since our architecture is based on Pushdown Computation (ELT), the fundamental rule of our M scripts is to keep local computational load at zero.
- No filters (
WHERE) inM: All row exclusions (e.g., omitting certain warehouses or dates) were already executed in the Business Central SQL engine via theDataItemTableFilterproperty. - 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. - 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).
4. M Development Checklist
Sección titulada «4. Checklist de Desarrollo M»- ✓ 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
Timeoutto 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.

