Biblioteca Online : ¡La Suscripción ENI por 9,90 € el primer mes!, con el código PRIMER9. Pulse aquí
¡Acceso ilimitado 24/7 a todos nuestros libros y vídeos! Descubra la Biblioteca Online ENI. Pulse aquí
  1. Libros
  2. Business Intelligence con Excel
  3. La preparación de los datos con Power Query
Extrait - Business Intelligence con Excel De los datos en bruto al análisis estratégico (2ª edición)
Extractos del libro
Business Intelligence con Excel De los datos en bruto al análisis estratégico (2ª edición) Volver a la página de compra del libro

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.

images/BI03.png

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:

images/BI004.png

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.

images/BIExc_002.png

Se abre una nueva ventana: se trata del editor de Power Query.

images/BIExc_003.png

Presentación de la interfaz

Vista inicial del editor de Power Query

El editor de consultas se presenta de la siguiente manera:

images/BI007.png

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.

images/BIExc_004a.png
images/BIExc_004b.png

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.

images/BIExc_005a.png
images/BIExc_005b.png

Agregar columna

Esta pestaña contiene herramientas para agregar diversos tipos de columnas: columnas condicionales, duplicadas, de fecha y hora, etc.

images/BI010.png

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.

images/BIExc_008.png

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.

images/BIExc_009.png

Aparece en pantalla el cuadro de diálogo Importar datos.

images/BI033.png

 Seleccione el archivo de Excel que desee y haga clic en el botón Importar.

Aparece un cuadro de diálogo llamado Navegador.

images/BI034.png

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.

images/BI035.png

Los datos se importan en forma de tabla al archivo de Excel activo.

images/BI036.png

En la parte derecha de la pantalla, el panel Consultas y conexiones indica que se ha colocado una consulta.

images/BI037.png

 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.

images/BI038.png

Aparece en pantalla el cuadro de diálogo Importar datos.

images/BIExc_063.png

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:

images/BIExc_010.png

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:

images/BI066.png

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:

images/BI095.png

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.

images/BIExc_023.png

Aparece en pantalla el cuadro de diálogo Crear tabla:

images/BI068.png

 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:

images/BIExc_024.png

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.

images/BI070.png

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.

images/BIExc_025.png

Aparece en pantalla el cuadro de diálogo Quitar filas superiores.

 Dado que deseamos eliminar la primera fila, introduzca la cifra 1.

images/BI072.png

 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.

images/BI114.png

 En la pestaña Agregar columna de la cinta de opciones, grupo De fecha y hora.

images/BI115.png

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.

images/BI116.png

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:

images/BIExc_039.png

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.

images/BIExc_040.png

 Ahora seleccione, por ejemplo, la columna Volumen de negocio.

El resultado es el siguiente:

images/BIExc_041.png

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.

images/BI122.png

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:

images/BI123.png

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.

images/BIExc_043.png

 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.

images/BI186.png

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:

images/BIExc_105.png

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:

images/BIExc_084.png

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...