Mejora un 90% el rendimiento de Power Query con SQLite
Power Query es una herramienta potente para consolidar, combinar y calcular datos, pero cuando trabajamos con millones de registros y calculos complejos por fila, el rendimiento se degrada drasticamente. En esta sesion partimos de un caso real: una tabla de 2.3 millones de pedidos donde hay que cruzar centros, zonas, descuentos, comisiones y rappels por tramos de volumen.
Con Power Query puro, el proceso completo tarda unos 90-110 segundos en una buena maquina (i7 13a gen, 32GB RAM). Pero el paso critico -- el calculo del rappel por tramos, que requiere buscar por fila en otra tabla -- sin usar Table.Buffer pasa de 25 segundos a 487 (casi 10 minutos). Y si ademas necesitas ordenar los 2 millones de filas, sube otros 200 segundos mas.
La alternativa: usar SQLite como motor intermedio. SQLite es una base de datos que se instala como un simple driver en tu PC, sin servidores ni licencias. La idea es volcar los datos de origen a SQLite (una sola vez o incrementalmente) y dejar que el motor SQL haga los joins y calculos pesados. Luego Power Query solo tiene que leer el resultado final.
El fichero adjunto (InfluLite_v1.xlsm) es un framework con macros VBA y un ribbon personalizado que facilita toda la operativa desde Excel:
- Crear tabla: seleccionas un rango en Excel y lo conviertes en tabla SQLite
- Insertar: anyades registros a una tabla existente (por lotes, ideal para cargas grandes)
- Query: ejecutas consultas SQL directamente desde una celda y ves el resultado en la hoja
- Tabla: traes una tabla entera de la base de datos a Excel
- Log: cada accion queda registrada para trazabilidad (saber que se cargo, cuando y como)
- Borrar log: deshace una carga concreta si detectas un error
Resultados de rendimiento:
| Metodo | Tiempo |
|--------|--------|
| Power Query completo | ~110 seg |
| Power Query (rappel sin buffer) | ~490 seg |
| SQLite via ODBC + Power Query | ~14-18 seg |
| SQLite consulta directa (VBA) | ~7 seg |
La mejora es de un 85-90% en el caso general, y la consulta directa SQL reduce el tiempo a menos de 10 segundos para los mismos 2.3 millones de registros. Ademas, la arquitectura incremental evita reprocesar historicos: si llega un fichero nuevo, solo insertas ese fichero en SQLite, no recalculas todo.
| Descargas SQLite | URL |
|------------------|-----|
| Driver (obligatorio) | http://www.ch-werner.de/sqliteodbc/ |
| SQLite Admin (recomendable) | https://sqlite.org/ |
Sesion impartida por Nacho Cardenal en el canal de Sergio Alejandro Campos (EXCELeINFO).
Cuando Power Query deja de ser la herramienta adecuada
Esta sesión, grabada con Sergio Alejandro Campos, es la continuación de una ponencia del Excel Code Summit que se cortó a mitad. El tema: por qué una consulta de Power Query que funcionaba perfectamente acaba tardando dos minutos, y cómo bajarla a menos de veinte segundos apoyándola en una base de datos local.
La tesis de partida no es que Power Query sea malo. Es que, como dice Nacho en la sesión, puedes cocinar cualquier plato en una sartén, pero hervir espaguetis en una sartén es incómodo porque no está pensada para eso.
En qué es bueno Power Query y en qué sufre
La sesión empieza con un diagnóstico honesto, por categorías:
- Bueno: consolidando muchos orígenes distintos, combinando tablas y generando columnas calculadas cuando ya tiene toda la información delante.
- Regular: cálculos por fila que necesitan mirar otra tabla, y agrupaciones sobre volúmenes grandes.
- Mal: cálculos complejos fila a fila, y sobre todo ordenar. Power Query no se diseñó para ordenar, y se nota.
Hay un cuarto problema que no es de rendimiento sino de arquitectura: si tienes 200 ficheros históricos y llega uno nuevo, Power Query recalcula los 201. Estás pagando el coste de reprocesar datos que no han cambiado.
El caso de prueba
El ejemplo usa una tabla de pedidos de unos 2,3 millones de filas repartidos en ocho ficheros, más cuatro tablas maestras: centros, descuentos por zona, comisiones por vendedor y tramos de rappel por volumen.
El cálculo encadena dos uniones secuenciales (pedido a centro, centro a zona y descuento), una unión simple (vendedor a comisión) y una búsqueda por tramos, que es la parte cara.
Un dato interesante que salió al preparar la sesión: repartir las mismas filas entre 8 ficheros grandes o entre 200 pequeños da tiempos de consolidación prácticamente iguales. La intuición de que menos ficheros irían más rápido no se cumple.
Los números
Con el equipo de la demo (procesador de 13ª generación, 32 GB de RAM):
- Consolidar los 2,3 millones de filas: 22 segundos.
- El proceso completo hasta el resultado agrupado: 110-115 segundos.
- Ordenar los 2 millones de filas: 204 segundos de media.
Y el dato que más impresiona, sobre la búsqueda por tramos:
- Con la tabla de rappeles metida en un búfer: 25 segundos.
- Sin el búfer, obligando a consultar la tabla en cada fila: 487 segundos, más de ocho minutos.
Es decir, los 90-110 segundos de referencia ya eran con la consulta optimizada a mano. Sin ese cuidado, el mismo cálculo se va a un cuarto de hora.
Hay además un efecto que rara vez se mide: las primeras actualizaciones tras abrir Excel penalizan mucho más, hasta 120 segundos extra, porque el programa tiene que resolver permisos de acceso a los ficheros. Cuando desarrollas, ya has actualizado diez veces y no lo notas; el usuario final, que abre el fichero una vez al día, sí lo sufre.
La alternativa: una base de datos dentro de tu carpeta
La propuesta es SQLite: un driver que descargas, instalas, y a partir de ahí tienes un motor de base de datos completo dentro de tu ordenador. Sin licencias, sin servidor, sin depender del departamento de IT. La base de datos es un simple fichero que vive junto a los demás.
Se complementa con dos piezas:
- SQLite Studio, un visor gratuito para ver las tablas y trazar qué hay dentro.
- Un ribbon propio en Excel, montado con VBA y un editor de cintas gratuito, con seis botones: crear tabla, insertar datos, lanzar una consulta, traer una tabla entera, consultar el registro de acciones y anular una carga.
Ese registro de acciones es una decisión de diseño que merece la pena copiar: cada carga queda marcada, de modo que si te equivocas al insertar puedes anular exactamente esa carga sin tocar el resto. Es el equivalente al nombre de fichero que Power Query te da al consolidar, pero para una base de datos.
Por qué esto va tan rápido: el query folding
Aquí está la razón técnica de fondo. Power Query tiene una capacidad llamada query folding: cuando el origen es una base de datos, traslada el peso del cálculo al propio origen en lugar de traerse todo y procesarlo en tu equipo.
El problema es que el query folding no funciona con ficheros de Excel ni de texto. Y tampoco con conexiones ODBC de forma automática. Pero con ODBC ocurre algo mejor: puedes escribir tú la consulta que se ejecuta en el origen. El resultado práctico es el mismo, porque el trabajo pesado lo hace el motor de base de datos y a Excel solo llega el resultado ya calculado.
El resultado
El mismo cálculo, con los mismos datos:
| Método | Tiempo |
|---|---|
| Power Query sobre ficheros | 110-115 s |
| SQLite consultado desde Power Query | 18 s |
| Consulta lanzada directamente desde el botón de Excel | 7 s |
Nacho se adelanta a la objeción evidente: "esto es trampa, porque Power Query está releyendo todos los ficheros cada vez". Y responde que precisamente ese es el punto. La carga a la base de datos se hace una vez, cuando llega cada fichero, en lugar de repetirse en cada actualización. No solo cambia el motor: cambia el proceso.
Como extra, una vez los datos están en la base de datos se abren opciones que con ficheros no existen: contar filas al instante, consultar agrupaciones sin montar toda la cadena de pasos, corregir un dato con una sentencia de actualización, o guardar una vista (una consulta grabada dentro de la base de datos que desde fuera se consulta como si fuera una tabla más).
Conclusión
El mensaje de la sesión no es "abandona Power Query" —Nacho se declara enamorado del lenguaje M— sino el de la frase que cita a mitad: para quien solo tiene un martillo, todo son clavos.
Conocer que existe una alternativa ligera, gratuita y que vive en tu propio ordenador cambia el tipo de soluciones que te atreves a plantear. Y en el camino obliga a hacerse preguntas de arquitectura que al principio nadie se hace: dónde viven los datos, cada cuánto cambian, y qué parte del proceso tiene sentido repetir en cada actualización.
Más casos con estas funciones
Más contenido de Excel en InflueXcel
- Extraer datos de archivos XML complejos: Power Query, VBA y Power Automate CasoSergio plantea un problema real con datos abiertos del gobierno español: archivos XML de licitaciones públicas con más de 30 niveles de anid
- Descomposición de Cholesky con matrices dinámicas: de VBA a LAMBDA+REDUCE CasoJuan Pablo lanza un reto al grupo: tiene una descomposición de Cholesky resuelta con VBA y quiere saber si se puede hacer con matrices dinám
- Precio más reciente por artículo: REDUCE, AGRUPARPOR y números complejos CasoNuevo reto interesante planteado por Miki: dada una tabla con artículos y sus precios históricos por año (múltiples filas por artículo), obt
- Generar la serie de Fibonacci con REDUCE, APILARV y LAMBDA CasoInteresante ejercicio compartido en la comunidad: generar los primeros N números de la serie de Fibonacci usando exclusivamente fórmulas de
- Resolver ecuaciones iterativas en Excel: Colebrook con LAMBDA recursiva y REDUCE CasoCaso excepcional que demuestra el poder de las funciones modernas de Excel en ingeniería. Un ingeniero industrial de casi 70 años, consultor
- Del VBA a una sola fórmula: calcular el RFC mexicano con LET, MAP y expresiones regulares CasoHector llega al grupo con un problema que no se ve todos los días: tiene resuelto el cálculo del RFC mexicano, pero lo tiene en VBA, y quier
- Saldo acumulado por mes: tres enfoques (REDUCE+BYROW, PIVOTARPOR+acumulado, MMULT) CasoJuan plantea una pregunta que parece sencilla y se acaba convirtiendo en tres clases magistrales sobre cómo recorrer una matriz mes a mes. T
- Power Query: rutas dinámicas para compartir ficheros sin romper conexiones CasoUn miembro de la comunidad plantea un problema muy frecuente: tiene una consulta de Power Query con una ruta de red fija (\\servidor\shared-
- Por qué ELEGIRFILAS falla con índices vacíos o erróneos CasoUn miembro de la comunidad comparte un problema que a muchos nos ha dado algún dolor de cabeza: intenta usar SI.ND() para manejar errores en
- Filtrar una tabla por una lista de valores: una LAMBDA propia y su inversa TutorialTe pasan una lista de 30 números de albarán y hay que sacar esas filas de una tabla de miles. Con el autofiltro es marcar casillas una a una