Transformar datos con Power Query

Nov 18, 2020 | DATOS, Importar y transformar | 0 Comentarios

Power Query es un complemento de Excel que nos permite detectar, conectar, combinar y refinar orígenes de datos para satisfacer nuestras necesidades de análisis. Se pueden hacer todas las transformaciones necesarias para convertir los datos en información preparada para el análisis, y finalmente almacenarlos, tanto en una hoja Excel como en Power Pivot. Los pasos a seguir serían:

Importar los datos (lo vimos en la sección anterior)
Transformar/limpiar los datos: darles forma a los datos según sus necesidades; el origen original permanece sin modificar
Cargar los datos en destino (lo vimos en la sección anterior)

¡No te pierdas ningún nuevo vídeo!

Visita mi canal de Youtube

Power Query se ha incluido en la pestaña “Datos” a partir de Excel 2016, si tienes una versión anterior aquí tienes los enlaces para descargarlos y para ver como habilitarlo:

Para acceder, tenemos que seleccionar “Datos” y “Consultas y conexiones”. A la derecha se abrirá una ventana con las conexiones existentes (ya vimos en el módulo anterior como crera una conexión. Haciendo doble-click en una de las conexiones se abrirá el editor de consultas.

También podemos clicar “Obtener datos” y ahí “Iniciar Editor de Power Query…”.

Power Query graba cada paso que se ejecute y, como vimos anteriormente, se guardan en el panel a la derecha. Desde aquí es posible:

Dar un nombre personalizado a cada paso clicando con el botón derecho y seleccionar “cambiar nombre”. Es muy útil si hay muchos pasos y cuando tengamos que volver para modificarlos.
Modificar un paso: al clicar la ruedecita o haciendo doble clic se abrirá una ventana donde podemos modificar lo que necesitemos.
Cambiar de orden: podemos arrastrar los diferentes pasos arriba y abajo. Ten en cuenta que las modificaciones son secuenciales y el orden de los pasos es muy importante.

Vamos ahora a ver las diferentes funcionalidades de Power Query en las diferentes pestañas del menú principal

Menú: Inicio

Elegir columnas: permite seleccionar las columnas que queremos tener disponibles y eliminar las demás. Se pueden marcar o desmarcar cualquiera de las columnas de la consulta, quedando disponibles sólo las que dejemos marcadas.

Quitar Columnas: permite eliminar columnas.

Conservar filas: permite mantener un conjunto de filas, ya sea del principio, del final, o un rango, eliminando las que queden fuera de la selección.

Quitar filas: permiten eliminar un conjunto de filas, ya sea del principio, del final, o un rango, manteniendo las que queden fuera de la selección. Es la operación contraria a la anterior.

Quitar duplicados: quita todas las filas de una tabla de Power Query, en el editor de consultas, donde los valores de las columnas seleccionadas duplican los valores anteriores.

Quitar Errores: si la consulta tiene filas con errores de datos, puede quitar estas filas de la consulta.

A-Z: ordenación ascendente de las filas en base a la columna o columnas seleccionadas.

Z-A: ordenación descendente de las filas en base a la columna o columnas seleccionadas.

Dividir Columna: podemos dividir una columna en varias, bien por un delimitador, bien por una longitud fija.

Agrupar por: podemos agrupar los valores de varias filas en un solo valor agrupando las filas según los valores de una o más columnas.

Tipo de datos: permite hacer conversiones de tipos de datos.

Usar la primera fila como encabezado: indica si la primera fila obtenida del origen son datos o son los encabezados de columnas. Si se pulsa, considera la primera fila como encabezados de columnas.

Remplazar valores: es como el buscar y reemplazar de Excel, indicamos el valor a buscar y el valor por el que lo queremos reemplazar.

Combinar consultas: crea una nueva consulta a partir de dos consultas existentes. Une columnas de dos tablas que tienen valores en común.

En este ejemplo tenemos una tabla con un cliente y su código postal, mientras que en la segunda tabla tenemos la población y la provincia de los códigos postales de España. Para añadir el detalle del código postal a la primera tabla hay que combinar las dos consultas.

Anexar consultas: esta operación crea una nueva consulta que contiene todas las filas de una primera consulta, seguidas de todas las filas de una segunda consulta (ambas con la misma estructura).

Por ejemplo, si tenemos una tabla con las ventas de enero y otra con las ventas de febrero, podemos obtener una sola tabla con las ventas de enero y febrero. Esta imagen será de ayuda para entender la operación:

Menú: Transformar

Agrupar por: podemos agrupar conjuntos de filas y aplicar funciones de agregación sobre ellos (suma, promedio, contar, mínimo, máximo, etc.). Por ejemplo, a partir de la tabla de clientes, podríamos agrupar por provincia y obtener una fila por cada provincia con el número de clientes que tenemos en ella. Está también en el menú ‘Inicio’.

Transponer: convierte las filas en columnas y viceversa. Hay que tener en cuenta que perdería los nombres de columnas originales, quedando nombradas como Columna1 a ColumnaN.

Invertir filas: invierte el orden de las filas. Aunque existe esta opción, es recomendable utilizar las opciones de ordenación y conseguir con ellas la ordenación más adecuada.

Contar filas: elimina las filas de la tabla y devuelve el número de filas de dicha tabla. Su única utilidad es almacenar el número de filas resultante, sin ningún otro dato adicional.

Tipo de datos: permite cambiar el tipo de datos de una columna. Está también en el menú ‘Inicio’.

Reemplazar valores: es como el buscar y reemplazar de Excel, indicamos el valor a buscar y el valor por el que lo queremos reemplazar. Está también en el menú ‘Inicio’.

Remplazar errores: reemplaza los errores por el valor que queramos.

Columna dinámica o “Pivotar” columna: podemos transformar las filas devueltas mediante una consulta en columnas de valor único.

Anular dinamización de columnas o “Despivotar” columna: para realizar la operación inversa, es decir transformar columnas en filas.

Mover: permite mover las columnas de sitio, hacia izquierda o derecha.

Dividir Columna: podemos dividir una columna en varias, bien por un delimitador, bien por una longitud fija. Por ejemplo, si tenemos una columna con el nombre y apellidos de los clientes, con las filas almacenadas de la siguiente forma: ‘apellido1 apellido2, nombre’, podríamos dividirla en dos columnas, una con el nombre y otra con los apellidos, indicando que el delimitador es la coma. Está también en el menú ‘Inicio’.

Formato: permite, por ejemplo, convertir columnas a mayúsculas o minúsculas, poner la primera letra de cada palabra en mayúsculas, eliminar espacios por la derecha e izquierda, y eliminar caracteres no imprimibles.

Combinar columnas: concatena las columnas seleccionadas en una sola, además se puede incluir un carácter separador entre el texto de cada columna. Por ejemplo, si tenemos una columna con el nombre y otra con los apellidos y queremos conseguir el formato ‘apellido1 apellido2, nombre’ lo podríamos hacer con esta opción. Es la acción opuesta a ‘Dividir Columna’.

Menú: Agregar Columna

Agregar columna de índice: En la cinta de opciones Editor de consultas, haga clic en Insertar columna de índice.

Agregar columna personalizada: Una columna personalizada es aquella en la que el valor de una celda se calcula con una fórmula que crea el usuario. Para más información sobre el lenguaje de formulación de Power Query, consulte Información sobre las fórmulas de Power Query: https://docs.microsoft.com/es-es/powerquery-m/power-query-m-function-reference

En la cinta de opciones Editor de consultas, haga clic en Insertar columna personalizada. A medida que escriba la fórmula y cree la columna, tenga en cuenta el indicador en la parte inferior de la ventana Agregar columna personalizada.

Si no hay ningún error, verá una marca de verificación verde y el mensaje No se han detectado errores de sintaxis.
Si hay un error de sintaxis, verá un icono de advertencia amarillo, junto con un vínculo a la ubicación en la que se produjo el error en la fórmula.

Al crear una nueva columna personalizada utilizaremos el lenguaje M de Power Query. El siguiente ejemplo es una columna personalizada donde concatenamos los valores de otras dos columnas.

Los siguientes son ejemplos de operaciones matemáticas utilizando tanto columnas como números.

Agregar una columna condicional: Puedes definir condiciones IF-THEN-ELSE en la consulta. Si se cumplen las condiciones, la columna condicional mostrará automáticamente los valores que ha especificado. Se puede hacer de forma manual en Columna personalizada o en Columna adicional.

En el cuadro Nuevo nombre de columna, escribe un nombre único para la nueva columna condicional.
Ahora puedes establecer las condiciones IF-THEN-ELSE.
En el cuadro de lista Nombre de columna, seleccione un nombre de columna.
En el cuadro de lista Operador, selecciona un operador.
En el cuadro de lista Valor, escribe el valor adecuado.
En el cuadro de lista Resultado, escribe el valor de resultado que debe mostrar la columna condicional si se cumple la condición IF.
Opcionalmente, si deseas agregar condiciones ELSE, haz clic en Agregar regla y, después, repite los pasos del 4 al 6.
Haga clic en Aceptar

Esta columna condicional también la podríamos hacer con código utilizando una columna personalizada.

Pasos aplicados de Power Query

Cada vez que el Editor de Power Query realiza una transformación en los datos, se muestra la fórmula asociada con la transformación en la barra de fórmulas. Para ver la barra de fórmulas, vaya a la pestaña Vista y, a continuación, seleccione Barra de fórmulas.

Editor de Power Query conserva todos los pasos aplicados para cada consulta como texto que se puede ver o modificar. En el panel derecho, o panel “Configuración de consulta”, es donde se muestran todos los pasos asociados a una consulta. Por ejemplo, en la imagen siguiente, la sección Pasos aplicados del panel “Configuración de consulta” refleja el hecho de que se acaba de cambiar el tipo de varias columnas. A medida que se aplican más pasos de conformación a la consulta, estos se capturan en la sección “Pasos aplicados”.

Si quieres profundizar más en estos temas te aconsejo visitar la página web de funciones de Power Query y el libro “Collect, Combine, and Transform Data Using Power Query in Excel and Power BI”.