Transformación de datos con Power Query

En este vídeo vamos a ver 5 ejemplos sobre transformación de datos utilizando la herramienta Power Query (o también modelado de datos).

Ejemplos:

• #1 Dividir columna.
• #2 Rellena abajo.
• #3 Agregar columna condicional.
• #4 Agregar columna a partir de ejemplos.
• #5 Convertir números en horas.

¿Qué ventajas aporta Power Query?

Power Query es un editor de consultas integrado en Excel que permite realizar procesos de Extracción, Transformación y Carga de datos (ETL).

Es muy útil cuando tenemos que realizar ciertas transformaciones sobre unos datos de forma periódica.

Por ejemplo, supongamos que cada mes nos llega un listado con datos de facturación de un proveedor y que siempre nos lo manda en un formato que debemos modificar para poder realizar las operaciones que nos interesan.

Utilizando Power Query podríamos realizar una vez las transformaciones de datos necesarias y, en los meses sucesivos, bastaría con un simple clic para transformar y cargar los datos actualizados, ya que se guardan los pasos aplicados y no es necesario volver a repetir las transformaciones.

Veamos a continuación cinco ejemplos de transformaciones de datos distintas que podemos aplicar con Power Query.

Ejemplo 1 – Dividir columna

En el primer ejemplo tenemos una Tabla1 en la cual vamos a dividir la columna del Código de Cliente para quedarnos en una columna solamente con los números y en otra columna con la parte de los códigos alfanuméricos.

Para ello vamos al Menú de Datos – Obtener y Transformar Datos – De una tabla o rango, cargamos el editor de Power Query y lo que hacemos es seleccionar la columna de Código Cliente y aplicamos Dividir columna – De dígito a no dígito, porque justamente después de los números se producen los textos y ya no vienen luego a continuación nuevos números.

Directamente tenemos la solución.

Cambiamos los nombres de los encabezados por CODNUM y CODTEXTO. Inicio – Cerrar y cargar – Cerrar y cargar en, y devolvemos la solución a la hoja de cálculo a la celda, por ejemplo, E2, Aceptar. Devuelve la query.

En todos estos ejemplos, si se produjera alguna nueva alta en la base de datos, introduciendo un nuevo registro, nos posicionaríamos en la consulta, clic derecho, Actualizar y tendríamos la consulta actualizada.

Ejemplo 2 – Rellena abajo

En el ejemplo 2, Rellenar Abajo, vemos que los productos que están cargados en vacíos vengan cargados con el nombre correspondiente de en este caso TV, Impresora y Horno. Cargamos el editor de Power Query. Con la Tabla2, seleccionamos la columna de Producto, clic derecho, Rellenar – Abajo.

Y ya vienen cargados correctamente los nombres de los productos en toda la columna. Menú de Inicio – Cerrar y Cargar – Cerrar y cargar en, y vamos a devolver la solución en la hoja de cálculo en la celda E2. Aceptamos.

Igualmente, que antes si se produjeran modificaciones en los datos originales, basta con hacer clic derecho y Actualizar en la consulta y vemos como se cargan correctamente los datos nuevos.

Ejemplo 3 – Agregar columna condicional

El ejemplo 3, Agregar Columna Condicional. Vamos a imaginar que cuando el producto sea TV, que nos ofrezca en una nueva columna Comisión con 100, cuando es Impresora 50 y en el resto de los casos 20.

Clic en Datos, Obtener y transformar datos y cargamos la consulta con el editor de Power Query. Agregar Columna Condicional, que nos lleva a una estructura if-then-else.

El nombre de la nueva columna va a ser Comisión. Si el nombre de la columna Producto fuera igual a TV (importante, tiene que haber coincidencias en mayúsculas y minúsculas, Power Query discrimina mayúsculas y minúsculas), que nos devuelva una comisión de 100. Agregamos una cláusula. En el caso de que el producto sea igual a Impresora que nos devuelva una comisión de 50 y en el resto de los casos que fuera de 20. Aceptamos y graba la comisión.

Cambiamos el tipo de datos a número entero y Cerrar y Cargar, Cerrar y Cargar en y cargamos nuestra consulta en la celda F2. Vemos como se corresponden las comisiones con lo esperado. En el resto de los casos que sería 20 la comisión, que no hay, vamos a hacer una variación, cambiamos una TV por Lavadora. Actualizamos nuestra consulta, clic derecho – Actualizar y vemos como registra el cambio automáticamente.

Ejemplo 4 – Agregar columna a partir de ejemplos

En el ejemplo 4, Agregar columna a partir de los ejemplos, queremos eliminar el número final que aparece en la columna de Producto. Cargamos el editor de Power Query. Vamos al menú de Agregar columna – Agregar columna a partir de los ejemplos, o atajo de teclado Ctrl+E.

Nos abre una nueva ventana, es muy similar al Relleno Rápido en Excel. Lo que le decimos es el primer elemento cómo lo queremos ver. Está entrando la inteligencia artificial. Escribimos TV y aplicamos Enter. Vemos que nos graba TV correctamente, eliminando el 2 al final, pero no lo hace correctamente para los microondas ni para las lavadoras.

Nos posicionamos en el primer valor en el que hay un fallo, en Impresora3 y escribimos cómo queremos que venga con la nueva salida Impresora y ha arreglado tanto Impresora como Microonda y ya estaría hecho. Cambiamos el nombre del encabezado, lo llamamos Productos correctos. Podemos eliminar la columna de Producto, clic derecho Quitar.

Vamos a Inicio – Cerrar y cargar – Cerrar y cargar en, nuestra consulta la llevamos a la celda E2. Cuando llegara algún nuevo producto, al aplicar clic derecho en nuestra consulta y actualizar, aparecería el nombre del producto sin ese número final.

Ejemplo 5 – Convertir números en horas

En el quinto caso, aparecen unos números que se asimilan a unas horas (horas, minutos y segundos), con seis o cinco cifras, en formato texto y queremos convertir los números a formatos apropiados de hora.

Cargamos la consulta en el editor de Power Query. Si hacemos un clic directamente en la columna Hora intentando cambiar el formato hora, va a detectar al Sustituir la actual con una nueva columna errores, esto no se puede hacer así directamente, porque tenemos números de 6 y 5 dígitos, almacenados como texto y lo está entendiendo mal.

Deshacemos el paso, desde Pasos aplicados, Tipo cambiado, lo deshacemos. Están los números almacenados a la izquierda y estos números están almacenados como texto.

Hacemos clic en la columna Hora, menú de Dividir columna, Por número de caracteres, queremos extraer los 2 últimos caracteres que vamos a asimilar que son los segundos. Número de caracteres 2, Dividir Una vez, lo más a la derecha posible. Nos hecho la extracción hacia la derecha de los 2 últimos dígitos.

Ahora le decimos que los 2 últimos dígitos de la columna Hora.1, que también se los lleve hacia la derecha. Seleccionamos esta columna, Dividir columna, Por número de caracteres, Número de caracteres 2, dividir una vez, lo más a la derecha posible. Aceptar.

Ya tenemos las 3 columnas con las horas, minutos y segundos totalmente separados. Ahora seleccionamos estas 3 columnas, clic derecho, Combinar columnas, que las combine con un Separador de Dos puntos (la separación que va a haber entre horas, minutos y segundos va a ser justo este separador, este delimitador). Nombre de la nueva columna, Hora correcta. Aceptamos.

El resultado nos lo vuelca ya con ese formateo con dos puntos para diferenciar horas, minutos y segundos, pero nos lo indica a la izquierda de la celda, luego sigue estando el número almacenado como texto.

Ahora sí que podemos hacer un clic en el botón de los formatos y elegir Hora y se produce un cambio en el almacenamiento y no nos depara ningún error.

Inicio, Cerrar y Cargar, Cerrar y cargar en, en la celda G2 cargamos nuestra consulta con las horas almacenadas correctamente.

Habría que formatear si quisiéramos correctamente la fecha en formato fecha. Volveríamos a la propia consulta, esto ya es un extra, elegimos en la columna Fecha el tipo y elegimos Fecha. Cerrar y cargar – Cerrar y cargar. Ya obtenemos correctamente la fecha en la consulta en formato fecha corta. Si cambiamos alguna celda del origen, actualizamos nuestra consulta con clic derecho – Actualizar y vemos cómo se produce la actualización de manera correcta.

Esto ha sido un ejemplo de cómo se puede realizar la transformación de datos con Power Query.

Otros vídeo tutoriales de Power Query