Ediciones ENI Editorial | líder en informática
La preparación de los datos con Power Query
Objetivo
Este primer capítulo va a permitirle familiarizarse y adquirir conocimientos concretos en el manejo de datos con Power Query.
A lo largo del capítulo se ofrecerán ejemplos de aplicaciones sencillas para ilustrar los conceptos abordados.
Al final se propondrá un caso práctico de aplicación inspirado en la experiencia profesional real.
Los archivos necesarios para la realización de los ejemplos y el caso práctico están disponibles para su descarga desde el sitio del editor.
Power Query, una herramienta para limpiar y manejar datos
Power Query es una herramienta de Microsoft especializada en el manejo y la transformación de datos.
Para el analista, esta etapa es probablemente más importante, ya que, sin datos estructurados, no hay nada que hacer.
Esta herramienta está disponible en Excel desde la versión 2010, pero también en Power BI Desktop, Microsoft Flow, Common Data Service y Analysis Service.
Cuando Microsoft comenzó a integrar herramientas de business intelligence en Excel, los usuarios no hicieron demasiado caso a Power Query, ya que preferían la mayor parte del tiempo las espectaculares posibilidades de cálculo del lenguaje DAX o bien las nuevas funciones gráficas.
En mi opinión, esto fue un error: Power Query es probablemente la herramienta más útil desde la llegada de las tablas dinámicas en 1993.
Power Query es lo que llamamos un ETL (Extract, Transform, Load).
-
Extraer: conectarse a una o varias fuentes para extraer los datos deseados.
-
Transformar: limpiar, formatear, agregar los datos.
-
Cargar: importar los datos, transformados o no en una ubicación específica para analizarlos.
De hecho, durante décadas, los usuarios de Excel han manejado los datos como si trabajasen con un ETL, solo que, hasta entonces, estas tareas se llevaban a cabo:
-
manualmente, con la consiguiente pérdida de tiempo, además del riesgo...
Primeros pasos
El objetivo de esta sección es el de proporcionar una visión de conjunto del funcionamiento de Power Query usando un ejemplo simple. El conjunto de las funciones se estudiará con más detalle en las secciones siguientes.
Presentación de los datos
Un refugio para animales dispone de una pequeña tabla de datos en la que se recoge el nombre, la especie, la fecha de acogida y la edad de diversos animales.
Los datos se presentan con esta forma:
Puede obtener los datos de este ejemplo en el archivo 1_animales.xlsx.
Importar los datos en Power Query
Abra el archivo 1_animales.xlsx.
Seleccione una celda incluida en la tabla de datos; por ejemplo, la celda A2.
En la pestaña Datos de la cinta de opciones, grupo Obtener y transformar datos, haga clic en el botón De una tabla o rango.
Se abre una nueva ventana: se trata del editor de Power Query.
Presentación de la interfaz
Vista inicial del editor de Power Query
El editor de consultas se presenta de la siguiente manera:
La cinta de opciones
La cinta de opciones se ubica en la parte superior de la pantalla y guarda cierto parecido con la de Excel. Se compone de cinco pestañas:
Inicio
Esta pestaña contiene las herramientas de transformación más habituales.
Aquí se puede quitar columnas o filas, ordenar, agrupar, combinar datos, dividir columnas, entre otras acciones.
Transformar
Esta pestaña incluye herramientas para modificar las columnas existentes.
Asimismo, desde aquí es posible dividir o agrupar columnas, transponer texto, utilizar las funciones de cálculo o trabajar con fechas y horas.
Agregar columna
Esta pestaña contiene herramientas para agregar diversos tipos de columnas: columnas condicionales, duplicadas, de fecha y hora, etc.
Algunas herramientas son comunes a las pestañas Transformar y Agregar columna, como por ejemplo las funciones de cálculo o de manejo de fechas y horas.
La barra de fórmulas
La barra de fórmula contiene código M.
Si no se muestra la barra de fórmulas, vaya a la pestaña Vista, grupo Diseño, y marque Barra de fórmulas.
M es un lenguaje de programación desarrollado por Microsoft y concebido especialmente para el trabajo y la modelización de datos....
La conexión a los datos
Power Query permite conectar Excel a una amplia gama de fuentes para filtrar, agregar y transformar los datos en bruto.
En Excel 2019, 2021 y Excel Microsoft 365, la herramienta de importación de datos Obtener datos se encuentra en la pestaña Datos de la cinta de opciones, en el grupo Obtener y transformar datos.
En esta sección vamos a ver como conectarse usando Power Query a algunas de las fuentes de datos más habituales.
Conexión a un archivo de Excel
Cree un nuevo libro de Excel.
En la pestaña Datos de la cinta de opciones, grupo Obtener y transformar datos, haga clic en el botón Obtener datos - De un archivo - Desde un libro de Excel.
Aparece en pantalla el cuadro de diálogo Importar datos.
Seleccione el archivo de Excel que desee y haga clic en el botón Importar.
Aparece un cuadro de diálogo llamado Navegador.
El botón Transformar datos permite seleccionar uno o varios elementos, hojas, tablas o rangos con nombre incluidos en el archivo de Excel.
Haga clic en uno de los elementos del archivo. Se muestra una vista previa en el lado derecho del cuadro de diálogo.
Es posible importar tablas y rangos con nombre al editor de Power Query; en cambio, no se pueden importar gráficos, tablas dinámicas, objetos o código VBA.
Desde el cuadro de diálogo Navegador, tiene dos opciones:
Cargar directamente los datos sin efectuar modificaciones
Haga clic en el botón Cargar para cargar los datos directamente en forma de tabla de datos.
Los datos se importan en forma de tabla al archivo de Excel activo.
En la parte derecha de la pantalla, el panel Consultas y conexiones indica que se ha colocado una consulta.
Para obtener otros métodos de visualización de los datos, haga clic en la flecha situada a la derecha del botón Cargar y escoja Cargar en.
Aparece en pantalla el cuadro de diálogo Importar datos.
Realizar transformaciones de datos
Desde el cuadro de diálogo Navegador, haga clic en el botón Transformar datos. Se abre el editor de Power Query:
Entonces podrá efectuar cambios en la tabla antes de importarla a Excel.
Conexión a una base de datos relacional: ejemplo con Access
En la pestaña Datos de la cinta de opciones, grupo Obtener y transformar datos, haga clic en el botón Obtener datos...
Trabajo básico con los datos
Para familiarizarse con los diferentes comandos básicos del editor de consultas de Power Query, vamos a realizar algunas acciones concretas usando una tabla de datos incluida en un archivo de Excel.
Puede acceder a los datos de este ejemplo en el archivo 4_trabajo_base.xlsx.
Un formador recoge en una tabla los participantes inscritos a sus cursos.
Los datos se presentan de la siguiente forma:
El objetivo de este ejercicio es transformar la estructura de la tabla con objeto de crear una tabla de datos estructurada, fácilmente analizable mediante una tabla dinámica:
-
en relación con los encabezados, única y exclusivamente los títulos de columnas,
-
sin filas, columnas ni celdas vacías.
Por otra parte, en aras de la claridad y para facilitar posibles búsquedas futuras, vamos a dividir la columna Nombre alumno en dos, a fin de que consten por separado el nombre y el apellido de los estudiantes.
El resultado final será el siguiente:
Importar datos en Power Query
Abra el archivo 4_trabajo_base.xlsx
Seleccione una celda contenida en la tabla de datos. Por ejemplo, la B3.
En la pestaña Datos - grupo Obtener y transformar datos, haga clic en el botón De una tabla o rango.
Aparece en pantalla el cuadro de diálogo Crear tabla:
Asegúrese de que los datos seleccionados por Excel se corresponden exactamente al rango de celdas deseado.
Desmarque la casilla La tabla tiene encabezados. En efecto: la primera fila de la tabla seleccionada no corresponde a los encabezados de las columnas.
Haga clic en el botón Aceptar.
Se abre el editor de Power Query:
Desde aquí, ya podemos transformar los datos.
Dar un nombre a la tabla
En el panel Configuración de la consulta, en el cuadro de escritura Nombre, dé a la tabla el nombre Curso_diseño.
A partir de ahora, la consulta se identificará con este nombre.
Eliminar las filas superfluas
La primera fila no se corresponde con el título de las columnas de la tabla; se trata más bien de una especie de título de la tabla.
En la pestaña Inicio - grupo Reducir filas, haga clic en el botón Quitar filas - Quitar filas superiores.
Aparece en pantalla el cuadro de diálogo Quitar filas superiores.
Dado que deseamos eliminar la primera fila, introduzca la cifra 1.
Haga clic en el botón...
Las herramientas de fechas y horas
Presentación de las herramientas
Power Query dispone de varias herramientas interesantes para trabajar con fechas y horas.
Estas herramientas se encuentran:
-
En la pestaña Transformar de la cinta de opciones, grupo Columna de fecha y hora.
En la pestaña Agregar columna de la cinta de opciones, grupo De fecha y hora.
La mayoría de las veces, las operaciones con fechas y horas se realizan desde el menú Agregar columna porque así se crean nuevos campos, en vez de transformar una columna individual.
Aplicación
Disponemos de una pequeña tabla de Excel con las fechas de nacimiento de varias personas.
Podrá acceder a los datos de este ejemplo en el archivo 6_fecha.xlsx.
El objetivo de este ejercicio es utilizar Power Query para extraer el año de nacimiento de la columna Fecha de nacimiento y, a continuación, calcular la edad de cada una de las personas.
Importar datos en Power Query
Abra el archivo 6_fecha.xlsx
Seleccione una celda incluida en la tabla de datos. Por ejemplo, la celda A2.
En la pestaña Datos - grupo Obtener y transformar datos, haga clic en el botón De una tabla o rango.
Se abre el editor de Power Query.
Extraer los años de nacimiento
Seleccione la columna Fecha de nacimiento.
En la cinta de opciones, seleccione la pestaña Agregar columna; en el grupo De fecha y hora, haga clic...
Perfilado de datos
El perfilado de datos es una herramienta de Power Query que permite a los analistas obtener una visión rápida de los datos.
Vamos a partir de estos datos y a importarlos en Power Query:
Puede encontrar los datos de este ejemplo en el archivo perfilado.xlsx.
Abra el archivo perfilado.xlsx.
Seleccione una celda incluida en la tabla de datos.
En la pestaña Datos - grupo Obtener y transformar datos, haga clic en el botón De una tabla o rango.
En la cinta de opciones de Power Query, seleccione la pestaña Vista.
En el grupo Vista previa de datos, marque las casillas Calidad de columnas, Distribución de columnas y Perfil de columna.
Ahora seleccione, por ejemplo, la columna Volumen de negocio.
El resultado es el siguiente:
Justo debajo del encabezado de la columna, encontramos el número de errores, el número de valores vacíos y la distribución de los valores.
Más abajo, en el lado izquierdo, hallamos diferentes Estadísticas de columna: Recuento, Error, Vacío, Mín., Máx., Promedio, etc.
Finalmente, en el lado derecho, encontramos otra Distribución de valores más detallada.
Según esta información, parece que en la columna Volumen de negocio hay un valor vacío y un valor duplicado.
Pase el cursor sobre la distribución y aparecerá un nuevo cuadro de diálogo....
Acciones avanzadas
Las columnas condicionales
Las columnas condicionales permiten definir nuevos campos en función de reglas y de condiciones lógicas, de la misma forma que las funciones Si y CAMBIAR de Excel.
La herramienta Columna condicional se encuentra en la pestaña Agregar columna, grupo General.
Aplicación: establecer categorías de animales en función de su peso
Un veterinario ha creado una tabla de Excel que incluye información diversa sobre los animales de compañía de sus clientes.
Los datos se presentan de la siguiente forma:
Podrá acceder a los datos de este ejemplo en el archivo 7_veterinario.xlsx.
En este ejemplo, el objetivo es crear una nueva columna denominada Clase que dependerá de las condiciones siguientes:
-
Si el peso del animal es superior a 10 kg, entonces su clase es pesado.
-
Si el peso del animal es superior o igual a 5 kg e inferior o igual a 10 kg, entonces su clase es intermedio.
-
Si el peso es inferior a 5 kg, entonces su clase es ligero.
Importar los datos en Power Query
Abra el archivo 7_veterinario.xlsx
Seleccione una celda incluida en la tabla de datos; por ejemplo, la celda A2.
En la pestaña Datos - grupo Obtener y transformar datos, haga clic en el botón Desde una tabla o rango.
Aparece en pantalla el editor de consultas.
Colocación de la columna condicional
En la pestaña Agregar columna, grupo General, haga clic en el botón Columna condicional.
Aparece en pantalla el cuadro de diálogo Agregar una columna condicional.
En el cuadro Nuevo nombre de columna, introduzca Clase.
Primera condición
En la lista desplegable Nombre de columna, escoja la columna Peso (kg).
En Operador, escoja es mayor que.
En Valor, introduzca 10.
En Salida, introduzca pesado.
Haga clic en el botón Agregar cláusula.
Segunda condición
En la lista desplegable Nombre de columna, escoja el campo Peso (kg).
En Operador, escoja es mayor o igual que.
En Valor, introduzca 5.
En Salida, introduzca intermedio.
Haga clic en Agregar cláusula.
Tercera condición
En la lista desplegable Nombre de columna, escoja el campo Peso (kg).
En Operador, escoja es menor que.
En Valor, introduzca 5.
En Salida, introduzca ligero.
En el cuadro de escritura De lo contrario, en la parte inferior...
Caso práctico: el presupuesto municipal
Presentación del caso
Usted es responsable del presupuesto de gestión cultural de un ayuntamiento. Al final de cada año, los directores de los distintos departamentos le envían sus propuestas de presupuesto para el próximo año.
Su tarea es recopilar los datos de cada uno de los departamentos para presentar un presupuesto provisional sintético de la dirección cultural a los representantes electos de la ciudad.
Su dirección comprende tres servicios: cultura, deporte y bibliomediateca.
Por supuesto, cada una de las tablas que ha recibido está presentada de una manera diferente, pero este año, gracias a Power Query, su trabajo se va a simplificar.
El propósito de este caso práctico es conectar Power Query a cada una de las tres tablas, modificarlas y crear una presentación sintética usando una tabla dinámica.
Para ello, vamos a trabajar con los datos de las tres tablas a fin de poderlas agrupar en una única tabla estructurada.
Esta tabla se presentará de la siguiente manera:
El informe que se espera obtener deberá presentar el importe autorizado por servicio y por concepto.
Preparación de los datos
Tabla n.° 1: el presupuesto provisional del servicio cultural
El presupuesto provisional del servicio cultural se encuentra en el archivo 12_PP_Cultura.xlsx.
Los datos se presentan de la siguiente forma:
Transformaciones necesarias
En la columna concepto, se hallan a la vez el concepto y la referencia al informe con el detalle del gasto. Para obtener la estructura deseada a partir de esta tabla, será preciso dividir la columna. El separador utilizado son los dos puntos.
Conectar la tabla a Power Query
Abra un libro de Excel en blanco.
En la pestaña Datos - grupo Obtener y transformar datos, haga clic en el botón Obtener datos - De un archivo - Desde un libro de Excel.
Aparece en pantalla el cuadro de diálogo Importar datos.
En su disco duro, busque y haga doble clic en el archivo 12_PP_Cultura.xlsx.
Aparece en pantalla el cuadro de diálogo Navegador.
Seleccione la tabla denominada PP_Cultura.
Haga clic en el botón Transformar datos.
Se abre el editor de Power Query
Dividir la columna
Seleccione la columna del concepto.
En la pestaña Inicio, grupo Transformar, haga clic en el botón...