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:
- que productos se vendieron en el 2018 y no se vendieron en el 2017;
- que productos se vendieron en el 2017 y no en el 2018;
- 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
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
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.
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.
