Plegado de consultas en Power Query: guía para optimizar el rendimiento
En proyectos de Business Intelligence con volúmenes de datos considerables, la diferencia entre una actualización de 5 minutos y una de 2 horas suele residir en un solo concepto: el plegado de consultas (o query folding). Como consultores, a menudo nos encontramos con modelos que tardan una eternidad en refrescarse no porque el servidor de base de datos sea lento, sino porque Power Query está descargando millones de filas innecesarias para procesarlas en la memoria local del equipo o de la capacidad de Power BI.
El plegado de consultas es la capacidad de Power Query para convertir los pasos de transformación definidos en la interfaz (lenguaje M) en una única consulta nativa (normalmente SQL) que se ejecuta directamente en el origen de datos. Si el plegado funciona, el motor de la base de datos realiza el filtrado, la agregación y la selección de columnas. Si se rompe, el Mashup Engine de Power Query toma el control, descarga los datos en bruto y aplica las transformaciones localmente. En este artículo analizaremos cómo monitorizar este proceso y qué decisiones técnicas tomar para evitar cuellos de botella.
Cómo comprobar si tu consulta se está plegando
La forma más rápida y conocida de verificar el plegado es hacer clic con el botón derecho en cualquier paso del panel «Pasos aplicados» en el Editor de Power Query. Si la opción Ver consulta nativa está habilitada, ese paso (y todos los anteriores) se están plegando correctamente al origen. Si la opción aparece en gris, el plegado se ha detenido en ese punto o en uno anterior.
Sin embargo, esta interfaz tiene limitaciones. A veces, la opción aparece deshabilitada aunque el plegado esté ocurriendo de forma parcial o mediante mecanismos internos que la UI no sabe representar. Para un diagnóstico profesional, lo ideal es utilizar las herramientas de diagnóstico integradas. En la pestaña «Herramientas» de Power Query, puedes activar el diagnóstico de pasos para ver exactamente qué sentencias SQL se están enviando al servidor. Esto es fundamental para entender cuánto tarda cada paso en Power Query y si el esfuerzo de optimización está dando frutos.
La importancia del orden de los pasos
El Mashup Engine de Power Query es secuencial. En el momento en que introduces un paso que el origen de datos no puede traducir a su lenguaje nativo, el plegado se rompe para ese paso y para todos los que vengan después. Por eso, la regla de oro en el desarrollo de procesos ETL es: filtra y selecciona lo antes posible. Si necesitas filtrar por una fecha de venta y eliminar diez columnas innecesarias, hazlo inmediatamente después de conectar a la tabla de origen. Si insertas un paso de transformación de texto complejo antes del filtrado, obligarás a Power Query a traerse todas las filas para luego descartarlas localmente.
Operaciones que rompen el plegado de consultas
No todas las transformaciones de Power Query tienen una equivalencia directa en SQL. Es vital conocer qué funciones son seguras y cuáles actúan como un muro para el plegado. La compatibilidad también depende del conector; un origen SQL Server soporta mucho más plegado que un conector de OData o Active Directory.
| Operación | Estado del Plegado | Alternativa Recomendada |
|---|---|---|
| Filtrado (Table.SelectRows) | Soportado (generalmente) | Realizar siempre al inicio. |
| Selección de columnas | Soportado | Evitar el uso de «Quitar otras columnas» con tipos dinámicos. |
| Agregaciones (Group By) | Soportado | Agrupar antes de realizar cálculos complejos. |
| Combinar consultas (Merge) | Parcial | Solo pliega si ambos orígenes están en el mismo servidor. |
| Transformaciones de texto (Custom) | A menudo rompe | Usar funciones estándar que tengan mapeo SQL. |
| Cambios de tipo de datos | Depende del punto | Realizarlos al final si no son necesarios para filtrar. |
Uno de los errores más comunes que veo en proyectos de retail es el uso de funciones de fecha personalizadas o transformaciones de texto como Text.Proper o Text.Combine. Estas funciones raramente se pliegan. Si necesitas limpiar nombres de clientes, es preferible hacerlo en una vista de SQL previa o aceptar que ese paso romperá el plegado y, por tanto, debe ser el último de tu consulta.
Estrategia de reordenación de pasos para recuperar el plegado
Si te encuentras con una consulta donde el plegado se rompe prematuramente, no asumas que es inevitable. Muchas veces, un simple movimiento de pasos puede devolver la eficiencia al modelo. Imagina que tienes una tabla de ‘Ventas’ con 50 millones de registros y aplicas los siguientes pasos:
- Origen (SQL Server).
- Navegación.
- Añadir columna personalizada (Cálculo de margen con una función compleja).
- Filtrar por ‘Año’ = 2023.
En este escenario, el paso 3 romperá el plegado. Power Query descargará los 50 millones de filas para calcular el margen y luego filtrará por año. Si mueves el paso 4 (Filtrar por Año) a la posición 3, el motor enviará un WHERE Año = 2023 al servidor, y solo se descargarán los datos de ese año. El ahorro de recursos es masivo.
// Ejemplo de código M donde el orden afecta al plegado
let
Origen = Sql.Database("servidor-bi", "dw_ventas"),
Ventas_Table = Origen{[Schema="dbo",Item="FactVentas"]}[Data],
// PASO CRÍTICO: Filtrar ANTES de transformar
FiltradoFecha = Table.SelectRows(Ventas_Table, each [Fecha] >= #date(2023, 1, 1)),
// Este paso rompe el plegado, pero ya hemos reducido el volumen de datos
CapitalizeProducto = Table.TransformColumns(FiltradoFecha, {{"Producto", Text.Upper, type text}})
in
CapitalizeProductoEsta técnica es fundamental cuando trabajamos con modelos semánticos optimizados, donde queremos que el motor VertiPaq reciba los datos ya limpios y reducidos.
El truco de Value.NativeQuery
A veces, el plegado automático de Power Query no es lo suficientemente inteligente o simplemente el conector no soporta una operación específica que tú sabes que SQL sí puede hacer. En estos casos, puedes forzar el comportamiento mediante Value.NativeQuery. Esto te permite escribir tu propio código SQL y pasarle parámetros, asegurando que el servidor haga el trabajo.
-- Consulta SQL optimizada que podemos pasar a Value.NativeQuery
SELECT
id_cliente,
SUM(importe) as TotalVentas
FROM dbo.Ventas
WHERE fecha > '2023-01-01'
GROUP BY id_clienteNota de consultor: El uso de consultas nativas manuales deshabilita el plegado de los pasos posteriores que añadas desde la interfaz de Power Query en muchos conectores. Úsalo solo cuando la optimización manual supere con creces lo que Power Query puede generar por sí solo.
Es importante recordar que si utilizas consultas nativas, pierdes parte de la visibilidad del linaje de datos en la interfaz, aunque a cambio ganas un control total sobre el rendimiento. Esta es una decisión técnica habitual en escenarios de DirectQuery, donde el rendimiento de la consulta SQL subyacente impacta directamente en la experiencia del usuario final.
Buenas prácticas en el diseño de ETL con plegado
Para mantener la salud de tus informes, especialmente en entornos de Microsoft Fabric o Power BI Service, sigue estos principios:
- Usa vistas de base de datos: En lugar de realizar transformaciones complejas en Power Query, crea una vista en el origen. Esto garantiza que Power BI reciba una tabla limpia y el plegado sea irrelevante porque la lógica ya está en el servidor.
- Cuidado con los tipos de datos: Cambiar el tipo de una columna de ‘Texto’ a ‘Número’ puede romper el plegado si el origen no puede realizar el cast de forma transparente. Hazlo al principio solo si es estrictamente necesario para un filtro.
- Evita funciones personalizadas: Las funciones de M creadas por el usuario suelen ser cajas negras para el motor de plegado. Si necesitas lógica reutilizable, intenta que sea compatible con los operadores estándar de Table.
- Gestión de nulos: El tratamiento de valores nulos es diferente en M y SQL. Asegúrate de leer sobre la gestión proactiva de errores para no introducir pasos que obliguen a evaluar fila por fila en local.
Conclusión y Checklist de optimización
El plegado de consultas no es un proceso de «todo o nada». Es un flujo que debemos proteger el mayor tiempo posible. Un error común es pensar que porque el origen es una base de datos SQL, todo se plegará automáticamente. La realidad es que un solo paso mal ubicado puede arruinar el rendimiento de todo el pipeline.
Checklist para tus consultas:
- ¿He verificado la opción «Ver consulta nativa» en los pasos clave de mi consulta?
- ¿He colocado los filtros (
Table.SelectRows) y la eliminación de columnas (Table.SelectColumns) al principio? - ¿Las transformaciones de texto o cálculos complejos están al final de la consulta?
- ¿He comprobado si una vista en SQL sustituye de forma más eficiente a mis pasos de Power Query?
- ¿Estoy combinando orígenes de datos diferentes que impiden el plegado del
Merge?
Preguntas frecuentes
¿El plegado de consultas funciona con archivos Excel o CSV?
No. El plegado requiere un lenguaje de consulta en el origen (como SQL o KQL). Al leer archivos planos, Power Query siempre debe descargar el archivo completo y procesarlo en el Mashup Engine local.
¿Por qué mi «Ver consulta nativa» está en gris si solo he filtrado filas?
Puede deberse a que el conector no soporta plegado (como algunos conectores Web) o a que el paso anterior ya rompió el plegado. Revisa la cadena de pasos desde el principio.
¿Es mejor usar SQL manual o los pasos de la interfaz de Power Query?
La interfaz es preferible por mantenibilidad y linaje. Usa SQL manual (Value.NativeQuery) solo cuando necesites optimizaciones específicas de rendimiento que Power Query no genera de forma eficiente, como HINTS de SQL o agregaciones muy complejas.
¿Cómo afecta el plegado al modo DirectQuery?
En DirectQuery, el plegado es obligatorio. Si una transformación no se puede plegar, Power Query mostrará un error indicando que la consulta no es compatible con este modo, ya que no puede descargar los datos para procesarlos en local.
