Comparar tablas con Power Query

lunes, julio 15, 2019

En la serie de notas sobre el tema vimos que usamos "Combinación de tablas" para buscar y extraer datos extraer datos de una tabla a otra, lo que en el Excel "clásico" hacemos con la función BUSCARV (o con una combinación de INDICE y COINCIDIR).

En esta nota vamos a considerar otra posibilidad que nos ofrece la combinación de tablas en Power Query: comparar tablas.

Este post es una versión aumentada (y un poco corregida) de mi anterior Comparar listas con Power Query 

A los efectos del ejemplo supongamos que queremos comparar dos tablas que contienen ventas de determinados productos, una contiene las ventas del año 2017 y la otra las del año 2018.

Nuestro jefe nos pide realzar las siguientes comparaciones:

  1. que productos se vendieron en el 2018 y no se vendieron en el 2017;
  2. que productos se vendieron en el 2017 y no en el 2018;
  3. que productos se vendieron en ambos años.

Empezamos por crear una consulta a cada tabla de datos



Las tablas de origen pueden estar en una base de datos, en un cuaderno de Excel o cualquier otro tipo de fuente. Al crear las consultas, también de fuentes remotas, como "solo conexión" evitamos duplicar datos (que ya existen en la fuente de origen) lo que nos ayuda a evitar problemas generados por redundancia de datos:
  • falta de sincronizado (cambios en el origen siempre se reflejan en los conexiones);
  • archivos innecesariamente "pesados".
Cuando combinamos datos entre dos tablas Power Query nos ofrece distintos modos de hacer la combinación, como podemos ver en el asistente de Combinación de tablas

Básicamente tres tipos de combinación: Externa (derecha, izquierda, completa); Interna y Anti (izquierda, derecha). Las descripciones que aparecen entre paréntesis describen la tarea de cada uno de los tipos. Las descripciones del tipo Anti no son lo suficientemente claras, para mi gusto. En "Anti izquierda dice "solo filas de la primera"; debería decir "sólo filas presentes en la primer tablas y no en la segunda". Lo mismo para "Anti derecha": "sólo filas presentes en la segunda tabla y no en la primera".

Para los ejemplos de esta nota vamos a usar los tres últimos tipos: Interna, Anti izquierda y Anti derecha.

Empecemos por el primer pedido de nuestro jefe:

Productos vendidos en el 2018 y sin ventas en el 2017

Apuntamos con el mouse a la consulta "2017", con un clic derecho abrimos el menú contextual y elegimos la opción Combinar


Esto abre el editor de consultas. En el diálogo de la combinación de tablas vemos que "2017" aparece como primera tabla, agregamos"2018" como segunda, seleccionamos el campo Producto en ambas tablas  y elegimos el tipo de combinación "Anti derecha"


Apretamos aceptar y veremos esto


La tabla "2018" ha sido agregada como columna con la doble flecha a la derecha del encabezamiento, ésto nos indica que podemos expandir la columna (de hecho, la tabla) y elegir qué campos queremos agrega

.
En nuestro ejemplo elegimos solamente "Producto" para ver que productos aparecen en las ventas de 2018 y no en la de 2017


Podemos ver que las columnas de la tabla 2017 contienen el valor "null", ya que los productos de la columna "2018.Producto" no tienen equivalente en el 2017. Ahora podemos eliminar las columnas de la tabla 2017 y cambiar el encabezamiento  de "2018.Producto" por algo más descriptivo como "Productos vendidos en 2018 y no en 2017" y cargar la tabla en la hoja



Ahora debemos encarar la segunda tarea:

Productos se vendieron en el 2017 y no en el 2018

Podemos repetir el proceso anterior cambiando el orden de combinación de las tablas con el mismo tipo de combinación (Anti derecha) o dejando el orden y cambiando el tipo de combinación a Anti izquierda.
Pero como soy un tanto holgazán voy a usar un atajo (a pesar de las sabias enseñanzas de mi abuelita: "Si los atajos fueran buenos no habría caminos").
Vamos a duplicar la consulta que acabamos de crear (Merge1)


Al duplicar obtenemos una nueva consulta idéntica a la de origen. Lo que haremos es modificar uno de los pasos aplicados. La consulta duplicada se abre en el editor de PQ y veremos el resultado de la consulta anterior. En el panel de pasos aplicados hacemos un clic al engranaje que aparece a la derecha del paso aplicado "Origen". Esto abrirá el diálogo de combinación de tablas


El la casilla "Tipo de combinación" cambiamos el tipo a "Anti izquierda"


 y apretamos "Aceptar"



Al expandir la columna para ver los productos de la tabla 2018, veremos que el resultado es "null" (nulo, no hay coincidencias).


Ahora tenemos que borrar los próximos dos pasos en el panel Pasos Aplicados y aplicar los correspondientes: cambiar el nombre de la columna "Productos" a "Productos vendidos en 2017 y no en 2018" y eliminar el resto de las columnas.


Ahora nos ocuparemos de la última tarea:

Productos se vendieron en ambos años

Obviamente vamos a utilizar el tipo de combinación "Interna" que nos traerá las filas donde el producto coincide en ambas tablas

Creamos una consulta combinando las tablas 2017 y 2018 y elegimos el tipo de combinación Interna


Al pie del formulario Power Query nos informa el resultado: 68 productos.
Para terminar nuestra tarea eliminamos todas las columnas excepto "Producto", cambiamos el encabezamiento a algo como "Productos vendidos en 2017 y 2018" y la cargamos a una hoja. De hecho, podemos cargar todas las consultas en una misma hoja


En este ejemplo nos hemos limitado a comparar los productos, pero el informe podría contener también los datos de ventas, por ejemplo, y en el informe de los productos vendidos en ambos años una comparación de las ventas.

Y recordemos, una vez desarrollado nuestro modelo, cada vez que actualicemos los datos de origen un simple clic a "Datos - actualizar todo" actualiza el informe en cuestión de segundos.

Seguir leyendo...

BUSCARV y Power Query - nota adicional

martes, julio 09, 2019

En su comentario a la tercera nota de la serie me preguntaba el lector Alfredo cómo haríamos si queremos mostrar todos los resultado. Es decir, filtrar sólo por el nombre del producto.
Obviamente tenemos que crear una condición de manera que si la celda F2 (Orden_de_aparición) esta en blanco no se aplique el filtro (y caso contrario si aplicarlo).

En todas las notas de la serie hemos hecho las transformaciones usando la interfaz de usuario, es decir, sin tener que escribir código por nuestros propios medios.
Power Query nos permite solucionar, por lo menos, el 80% de nuestros problemas de transformación de datos directamente a través de la interfaz de usuario. Esto significa que no necesitamos conocer el lenguaje M y escribir código por nuestra cuenta para solucionar problemas de manejo de datos.

En el caso que nos consulta Alfredo si tendremos que agregar una línea de código. Si bien esto supone ya conocimientos más avanzados, también el usuario principiante o intermedio se beneficiará descubriendo las increíbles posibilidades que nos ofrece Power Query.

En el modelo de la nota anterior introducíamos el nombre del producto en la celda F1 (que habíamos ligado al nombre definido "Producto") y el orden de aparición buscado en la celda F2 (asimismo ligada al nombre definido "Orden_de_aparición"). Al activar "Datos-Actualizar todo", obteníamos el resultado en la celda E6. Si dejamos la celda F2 en blanco, la celda E6 también quedará en blanco. Esto quiere decir que cuando Power Query no encuentra el orden de aparición buscado, el resultado es "null".



Cuando aplicamos pasos en el editor Power Query va generando el código correspondiente a esos pasos



Para crear la posibilidad de mostrar todas las filas del producto en el caso de dejar en blanco la celda F2 (Orden_de_aparición) tendremos que intervenir en el anteúltimo paso ("Filas filtradas1"). Veamos el código creado por Power Query para hacer el filtrado; podemos verlo en la barra de las fórmulas


o en el editor avanzado


En el editor avanzado reemplazamos el paso #"Filas filtradas1" por el siguiente código:

 #"Filas filtradas1" = if Orden_de_aparición<>null then Table.SelectRows(#"Índice agregado",
            each ([Índice] = Orden_de_aparición))
                else #"Índice agregado",



(para facilitar la lectura dividimos el código en varias líneas apretando Enter; de la misma manera podríamos escribir el código en una sola línea).
Como pueden ver estamos creando una condición: Si (if) el valor del parámetro Orden_de_aparición no es "null" (vacío) entonces aplicamos la función Table.SelectRows; en caso contrario (else) nos referimos al paso anterior (#"Índice agregado") sin tomar ninguna acción, que es la forma de ignorar el filtrado.
En nuestro ejemplo si dejamos la celda F2 vacía



tendremos este resutlado



y al cargar la consulta en la hoja y apretar "Datos-Actualizar todo"  veremos








Seguir leyendo...

Power Query o por qué volví a publicar en el blog

sábado, julio 06, 2019

Comencé a publicar notas en este blog en el año 2006 a sugerencia de un compañero de trabajo. Todo el tiempo te consultan, me decía, ¿no te sería más práctico publicar las respuestas en un sitio o blog?

Considerando la propuesta dediqué unos días a leer los distintos blogs y sitios sobre Excel que habitaban en esa época el espacio WWW. Mi primera conclusión fue que no había nada que yo pudiera agregar a lo ya publicado, en particular en inglés pero también en castellano. Hasta que encontré la siguiente frase: "No importa cuán bueno seas, siempre habrá uno mejor que ti". Despojado del deseo, oculto pero deseo al fin, de ser el mejor comencé a publicar con la meta de compartir mis experiencias como usuario de Excel y ayudar a otros a avanzar en el uso de esta herramienta.

En setiembre del 2017, once años y 745 posts más tarde llegué al punto donde consideré cumplido mi ciclo.
Sin embargo, aquí estoy nuevamente, publicando y dialogando con mis lectores poco menos de dos años después de mi "retiro". ¿Que ha pasado?

En los últimos años Microsoft ha  sumado nuevas herramientas a Excel: Tablas ("Listas" en Excel 2003), Power Query y Power Pivot.
Esta nuevas herramientas han revolucionado la forma en que debemos trabajar con Excel. No menos que la introducción de las tablas dinámicas o del Vba.
Y pongo el acento en "debemos" porque para aprovechar estas herramientas deberemos dejar viejos hábitos y entender cabalmente cual es la mejor práctica de trabajo con el nuevo Excel. Y este es el motivo que me ha llevado a renovar mi actividad en el blog: propagar el uso de esta nuevas herramientas y las buenas prácticas en la transformación y manejo de datos en Excel.

Buen fin de semana a todos.


Seguir leyendo...

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP