Como conozco las tareas del contador no me cabe duda de que Power Query es una herramienta indispensable para él, en términos de ahorro de tiempo y errores. Pero si algo he aprendido con los años es no entrar en discusiones estériles.Yendo al grano de la cuestión d este post: si muchas tareas se pueden hacer con BUSCARV, ¿por qué usar Combinar consultas del Power Query en su lugar?
Muchas tareas que realizamos con Power Query pueden hacerse, sin dudas, con Excel Clásico y para quien lleva mucho tiempo trabajando con Excel Clásico la inversión en tiempo y esfuerzo para aprender Power Query puede parecer infundada.
En esta nota voy a intentar exponer los criterios a tomar en cuenta al decidir si haremos una tarea con Excel Clásico o con Power Query.
Empecemos por decir qué es Power Query. Es una herramienta destinada a preparar datos para un posterior análisis. Es una herramienta ETL (Extract - Transform - Load) es decir, que nos permite:
- extraer datos de una o más fuentes y en este último caso combinarlos;
- transformarlos, es decir, adaptarlos a nuestros requerimientos;
- cargarlos (load) al medio donde haremos nuestros análisis (dejándolos como "solo conexión", cargándolos a una hoja de Excel o al modelo de datos).
Si bien podemos crear reportes con Power Query este herramienta no está diseñada para analizar datos. Para esas tareas tenemos a Excel, por lo general usando tablas dinámicas, o PowerPivot.
Por otro lado es posible realizar tareas ETL con Excel por lo que vamos a analizar los pro y contras de cada herramienta en las "zonas superpuestas". Veamos el caso de BUSCARV frente a Combinar consultas.
Una tarea clásica con Excel es combinar datos de dos tablas usando BUSCARV o una combinación de INDICE con COINCIDIR. El uso de estas funciones es relativamente sencillo y su dominio es el certificado de entrada del usuario principiante a usuario medio-avanzado.
Pero BUSCARV tiene varias debilidades y es en estas situaciones que preferiremos usar Combinar consultas del Power Query.
1 - Más de una coincidencia por valor de búsqueda.
Cuando hay más de una coincidencia en la tabla de búsqueda BUSCARV da como resultado la primer coincidencia. Si queremos obtener una en particular, por ejemplo la segunda, deberemos aplicar fórmulas complicadas (y fórmulas complicadas siempre conllevan el riesgo de tener errores difíciles de detectar). La solución con Power Query es más sencilla y fácil de controlar.
2 - Búsqueda con más de un criterio.
Con Excel Clásico podemos hacer búsquedas basadas en más de un criterio usando técnicas como las que muestro en esta nota. Pero, nuevamente, tendremos que aplicar fórmulas complejas o trabajar con tablas dinámicas y segmentación de datos. Por su lado Power Query permite hacer combinación de consultas en base a varios criterios, en forma natural y con facilidad.
3 - Problemas con el tiempo de recálculo de la hoja.
El uso intensivo de fórmulas con BUSCARV tiene un precio elevado en términos de tiempo de recálculo. Eventos como filtrado de la tabla hacen que Excel recalcule todas las fórmulas de la hoja. En el departamento de logística de la empresa donde trabajo usan una hoja para planificar los envíos de cada semana. Una de las columnas de la tabla contenía una fórmula con BUSCARV para poner la categoría de cada producto. La tabla consiste en una 9000 filas en promedio (varía de semana en semana). Una vez actualizadas las categorías el coordinador emplea el filtro de datos a una caegoría específica para ver qué productos hay que enviar. El recálculo de la hoja, cada vez que se aplica un filtro, dura más de cinco minutos (!) y en ciertos casos produce el colapso de Excel.
En el mismo modelo pero usando Combinar consultas el tiempo de recálculo se redujo a menos de dos segundos.
4 - Más de un resultado por valor de búsqueda.
Hay situaciones en las que buscamos todas las coincidencias de un valor en la tabla de búsqueda. Por ejemplo, todos los números orden de un producto.
Supongamos esta lista de órdenes de clientes (la tabla tiene 2155 filas)
y esta lista de 10 productos escogidos. Nuestra tarea es mostrar todas las órdenes de cada producto de esta lista.
BUSCARV puede devolver solamente un resultado así que no nos sirve para esta tarea. Pero con Combinar consultas la hacemos con unos pocos clics.
1 - creamos las consultas a ambas tablas
En nuestro ejemplo las tablas se encuentran en las hojas del cuaderno por lo que creamos las consultas como "sólo conexión".
2 - Creamos una consulta combinando ambas tablas de esta manera
3 - Expandimos la columna [Ordenes] seleccionando sólo la columna [Orden], ordenamos la columna [Producto] alfabéticamente y así obtenemos una tabla con todos los productos elegidos y sus órdenes de compra
Una variante interesante es crear el informe de manera que haya solamente una fila por producto y una columna con todas las órdenes o varias columnas con las órdenes.
Para hacerlo vamos a usar Agrupar por agrupando por [Producto] la consulta de arriba
Ahora vamos a editar el código creado automáticamente abriendo el Editor avanzado (tal como mostré en esta nota). El código en el editor avanzado se ve así
Vamos a cambiar a fila 5 de esta manera agregando {"Ordenes", each [Orden]}
En en el editor de Power Query veremos
Hemos agregado la columna [Ordenes] que podemos expandir haciendo un clic sobre la doble flecha que aparece a la derecha del encabezamiento de la columna. Al hacerlo vemos que hay dos posibilidades de expansión
Elegimos Extraer valores eligiendo algún delimitador, en este ejemplo "punto y coma" con este resultado
Podemos decidir dejar o eliminar la columna [Recuento] y cargar la consulta en un hoja de Excel. Tendremos así una fila por cliente con una columna que muestra todas las órdenes del cliente.
Alternativamente podemos dividir la columna [Ordenes] en varias columnas.
Una tarea clásica con Excel es combinar datos de dos tablas usando BUSCARV o una combinación de INDICE con COINCIDIR. El uso de estas funciones es relativamente sencillo y su dominio es el certificado de entrada del usuario principiante a usuario medio-avanzado.
Pero BUSCARV tiene varias debilidades y es en estas situaciones que preferiremos usar Combinar consultas del Power Query.
1 - Más de una coincidencia por valor de búsqueda.
Cuando hay más de una coincidencia en la tabla de búsqueda BUSCARV da como resultado la primer coincidencia. Si queremos obtener una en particular, por ejemplo la segunda, deberemos aplicar fórmulas complicadas (y fórmulas complicadas siempre conllevan el riesgo de tener errores difíciles de detectar). La solución con Power Query es más sencilla y fácil de controlar.
2 - Búsqueda con más de un criterio.
Con Excel Clásico podemos hacer búsquedas basadas en más de un criterio usando técnicas como las que muestro en esta nota. Pero, nuevamente, tendremos que aplicar fórmulas complejas o trabajar con tablas dinámicas y segmentación de datos. Por su lado Power Query permite hacer combinación de consultas en base a varios criterios, en forma natural y con facilidad.
3 - Problemas con el tiempo de recálculo de la hoja.
El uso intensivo de fórmulas con BUSCARV tiene un precio elevado en términos de tiempo de recálculo. Eventos como filtrado de la tabla hacen que Excel recalcule todas las fórmulas de la hoja. En el departamento de logística de la empresa donde trabajo usan una hoja para planificar los envíos de cada semana. Una de las columnas de la tabla contenía una fórmula con BUSCARV para poner la categoría de cada producto. La tabla consiste en una 9000 filas en promedio (varía de semana en semana). Una vez actualizadas las categorías el coordinador emplea el filtro de datos a una caegoría específica para ver qué productos hay que enviar. El recálculo de la hoja, cada vez que se aplica un filtro, dura más de cinco minutos (!) y en ciertos casos produce el colapso de Excel.
En el mismo modelo pero usando Combinar consultas el tiempo de recálculo se redujo a menos de dos segundos.
4 - Más de un resultado por valor de búsqueda.
Hay situaciones en las que buscamos todas las coincidencias de un valor en la tabla de búsqueda. Por ejemplo, todos los números orden de un producto.
Supongamos esta lista de órdenes de clientes (la tabla tiene 2155 filas)
y esta lista de 10 productos escogidos. Nuestra tarea es mostrar todas las órdenes de cada producto de esta lista.
BUSCARV puede devolver solamente un resultado así que no nos sirve para esta tarea. Pero con Combinar consultas la hacemos con unos pocos clics.
1 - creamos las consultas a ambas tablas
2 - Creamos una consulta combinando ambas tablas de esta manera
3 - Expandimos la columna [Ordenes] seleccionando sólo la columna [Orden], ordenamos la columna [Producto] alfabéticamente y así obtenemos una tabla con todos los productos elegidos y sus órdenes de compra
Una variante interesante es crear el informe de manera que haya solamente una fila por producto y una columna con todas las órdenes o varias columnas con las órdenes.
Para hacerlo vamos a usar Agrupar por agrupando por [Producto] la consulta de arriba
Ahora vamos a editar el código creado automáticamente abriendo el Editor avanzado (tal como mostré en esta nota). El código en el editor avanzado se ve así
1: let
2: Origen = Table.NestedJoin(Productos, {"Producto"}, Ordenes, {"Producto"}, "Ordenes", JoinKind.LeftOuter),
3: #"Se expandió Ordenes" = Table.ExpandTableColumn(Origen, "Ordenes", {"Orden Nro."}, {"Orden Nro."}),
4: #"Filas ordenadas" = Table.Sort(#"Se expandió Ordenes",{{"Producto", Order.Ascending}}),
5: #"Filas agrupadas" = Table.Group(#"Filas ordenadas", {"Producto"}, {{"Recuento", each Table.RowCount(_), type number}})
6: in
7: #"Filas agrupadas"
Vamos a cambiar a fila 5 de esta manera agregando {"Ordenes", each [Orden]}
1: let
2: Origen = Table.NestedJoin(Productos, {"Producto"}, Ordenes, {"Producto"}, "Ordenes", JoinKind.LeftOuter),
3: #"Se expandió Ordenes" = Table.ExpandTableColumn(Origen, "Ordenes", {"Orden"}, {"Orden"}),
4: #"Filas ordenadas" = Table.Sort(#"Se expandió Ordenes",{{"Producto", Order.Ascending}}),
5: #"Filas agrupadas" = Table.Group(#"Filas ordenadas", {"Producto"}, {
6: {"Recuento", each Table.RowCount(_), type number},
7: {"Ordenes", each [Orden]}
8: })
9: in
10: #"Filas agrupadas"
En en el editor de Power Query veremos
Elegimos Extraer valores eligiendo algún delimitador, en este ejemplo "punto y coma" con este resultado
Podemos decidir dejar o eliminar la columna [Recuento] y cargar la consulta en un hoja de Excel. Tendremos así una fila por cliente con una columna que muestra todas las órdenes del cliente.
Alternativamente podemos dividir la columna [Ordenes] en varias columnas.
Hola Jorge
ResponderBorrarMuy interesante el analisis... Solo me queda la duda de si mis archivos de bases de datos tienen un máximo de 5000 registros me conviene o no usar power query
Para dar una respuesta hay que tomar en cuenta varias consideraciones. Por ejemplo, que otras funciones hay en la hoja. Si todo lo que hay es cinco mil formulas con la función BUSCARV, el tiempo de recalculo puede ser "soportable", dependiendo de los recursos disponibles en tu máquina. Pero si cada tanto hay que hacer operaciones que implican recalcular la hoja, como Autofiltro, el trabajo con la hoja puede ser bastante lento (de hecho fue el caso que me dio la inspiración para el post).
BorrarSi se trata de un modelo donde los datos cambian tanto, sin lugar a dudas Power Query es la herramienta a usar. En particular si la cantidad de registros cambia en cada "set" de datos.