Dividir columnas con Power Query

Una tarea frecuente cuando de transformar datos se trata es dividir una columna en varias. Desde sus primeras versiones Excel cuenta con una herramienta para dividir columnas: Texto en Columnas, denominación que nunca logré entender; "Dividir columna" (Split column) me parece más apropiado.

Pero no es éste el tema del post. Vamos a mostrar por que Dividir columna del Power Query es superior a Texto en Columnas del Excel clásico.

Veamos el siguiente caso. Hemos recibido una lista de clientes con una única columna que incluye el nombre del cliente, la dirección y la ciudad.

Nuestra tarea es extraer al ciudad de cada línea. Aparentemente Texto en columnas lo puede hacer con facilidad usando la coma como separador de los campos. Pero si nos fijamos con atención veremos que la fila 2 aparecen dos comas, una separa el nombre de la dirección y la otra la dirección de la ciudad, pero en la fila 3 hay tres comas (una precede el numero 23, la otra inmediatamente después del número).
Si usamos  Texto en columnas obtendríamos ésto:

En algunos casos la ciudad aparece en la columna C y en otros en la columna D. Remediar esta situación en la hoja de Excel sería extenuante (supongamos una lista de cientos o tal vez miles de clientes).

Si usamos Power Query la tarea se convierte en "coser y cantar". Como siempre, empezamos por crear una conexión a la tabla

En el menú Inicio-Transformar abrimos las opciones de Dividir Columna-por delimitador

En el diálogo que se abre

Elegimos la opción Delimitador situado más a la derecha  y apretamos Aceptar y ....

El nombre de la nueva columna lo establece Power Query. Podemos cambiarlo haciendo un doble clic sobre el encabezamiento o usando el menú contextual Cambiar nombre. También podemos editar el código del paso aplicado usando la barra de fórmulas

cambiando "Clientes.2" por el nombre deseado (digamos, Ciudad). Sencillamente lo editamos en la barra y apretamos Enter.

Ahora tenemos que separar el nombre del cliente de la dirección. Como ya habrán supuesto, usamos la opción Delimitador situado más a la izquierda.

Todo el proceso de división de la columna usando Power Query puede verse en este video

Otra forma de comparar tablas con Power Query

En la nota anterior sobre el tema mostramos como comparar tablas con Power Query para encontrar, por ejemplo, diferencias entre ambas tablas. Por ejemplo, podemos comparar dos listas de nombres para controlar que nombres de las lista1 no aparecen en la lista2 o viceversa.

Todas las tareas las hicimos usando la interfaz del usuario. Es decir, sin necesidad de escribir o modificar ninguna línea de código para obtener el resultado. Sin embargo con cada paso que aplicamos, y que vemos reflejado en el panel de "Pasos Aplicados", Power Query está escribiendo líneas de código en su lenguaje, conocido como el lenguaje "M".

Voy a aprovechar esta nota para mostrar como podría comparar dos listas un usuario con ciertos conocimientos del lenguaje. Para lo cual voy a dedicar algunas líneas al lenguaje M.

El lenguaje M comprende objetos y funciones. Entre los objetos voy a mencionar las Tablas y las Listas. La diferencia entre una tabla y una lista, dicho en forma general, es que la lista siempre tendrá una sola columna. Así, por ejemplo, podemos tomar una columna de una tabla y convertirla en Lista y, a su vez, podemos convertir una lista en Tabla.
El motivo para estas transformaciones es que cada objeto, Listas y Tablas en nuestro caso, tiene su propia colección de funciones.
Power Query no tiene incorporado  un asistente de funciones de manera que para consultar qué funciones existen tenemos que abrir está página (por ahora no se ha publicado una versión en castellano).

En nuestro ejemplo vamos a usar la función List.Difference. Las funciones están organizadas por categorías; la nuestra se encuentra en la categoría List Functions





Como vemos la función utiliza dos variables, las listas a comparar, y una tercera opcional que ignoraremos en esta nota.

"List" que antecede al nombre de la función nos indica que se trata de una que actúa sobre Listas. Esto quiere decir que para usarla tendremos que convertir, previamente, las columnas de nuestras tablas a listas.

Para evitar un tsunami de palabras en un largo post, voy a mostrar y explicar el proceso con este video



Ahora bien, si podemos hacer la comparación usando la interfaz de usuario, ¿por qué hacerlo escribiendo código?
La interfaz de usuario de Power Query es muy poderosa; nos permitirá resolver algo así como el 40% de nuestras necesidades de transformación de datos. Pero no todos los casos se pueden resolver sin escribir código o de la manera más eficiente.

Según Gil Raviv estas son las etapas del aprendizaje de Power Query y el lenguaje M

1. Solo interfaz - nos permite solucionar aproximadamente el 40% de los problemas.
2. Edición básica en la barra de fórmulas - Si bien no tenemos sólidos conocimientos de M y las funciones, si podemos asociar elementos en las fórmulas con las pasos dados con la interfaz y lograr ciertas transformaciones. En esta etapa ya podemos resolver el 60% de los problemas.
3. M en columnas personalizadas - En esta etapa dominamos el uso de columnas personalizadas y podemos crear fórmulas efectivas; dominamos el uso de condicionales y los operadores Booleanos. En esta etapa ya nos enfrentamos con éxito al 80% de los problemas.
4. Funciones personalizadas - En esta etapa ya dominamos el uso de funciones personalizadas lo que nos permite reusar transformaciones que hemos creado. Ahora ya podemos resolver el 95% de los problemas.
5. Iteraciones avanzadas - En esta etapa ya sabemos enfrentarnos a escenarios complejos y es cuando empezamos a usar funciones como List.Accumulate y List.Generate para crear iteraciones de transformaciones. Ya sabemos resolver el 99% de los desafíos que nos presenten; el 1% restante pueden ser resueltos usando otras herramientas.

Gil describe una sexta etapa sobre la cual no me voy a extender ya que está reservada a los super-humanos (o Cyborgs o seres de otras galaxias).
Para los simples mortales un buen punto de comienzo es el curso de Ivan Pinar Dominguez

Comparar tablas con Power Query

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.