Análisis moderno de datos en Excel

Capítulo 4. Transformación de columnas en Power Query

Este trabajo se ha traducido utilizando IA. Agradecemos tus opiniones y comentarios: [e-mail]

El capítulo 3 se centró en familiarizarse con la operación sobre filas; el enfoque de este capítulo se desplaza a las columnas. Este capítulo incluye varias técnicas, como transformar el caso de las cadenas, reformatear columnas, crear campos calculados, etc. Para seguir las demostraciones de este capítulo, consulta ch_04.xlsx en la carpeta ch_04 del repositorio del libro. Adelante, carga la tabla rentals en Power Query.

Cambiar la caja de la columna

La consulta Power agiliza el proceso de conversión de columnas de texto entre minúsculas, mayúsculas y mayúsculas "propias" (en las que cada palabra va en mayúsculas). Para probar esta capacidad, pulsa la tecla Ctrl y selecciona simultáneamente las columnas Title y Artist Name. A continuación, haz clic con el botón derecho del ratón en una de las columnas, navega hasta Transformar → Poner en mayúsculas cada palabra, como se muestra en la Figura 4-1.

Figura 4-1. Cambiar mayúsculas y minúsculas en Power Query

Observa que Title y Artist Name carecen de espacios después de los dos puntos y las comas. Para solucionarlo, con ambas columnas aún seleccionadas, haz clic con el botón derecho del ratón en cualquiera de ellas y elige Reemplazar valores. En , en el cuadro de diálogo Reemplazar valores, busca ":" y sustitúyelo por dos puntos seguidos de un espacio, como se muestra en la Figura 4-2.

Figura 4-2. Sustitución de valores en Power Query

A continuación, aplica el mismo proceso a las comas: sustitúyelas por una coma seguida de un espacio.

Como se demostró en el Capítulo 3, Power Query captura cada paso que realizas sobre los datos en la lista Pasos Aplicados. Esta función facilita enormemente la auditoría de los cambios de texto en comparación con el proceso convencional de buscar y reemplazar .

Delimitación por columna

En el Capítulo 3, aprendiste a dividir texto delimitado por comas en filas. Ahora es el momento de hacer lo mismo con las columnas. Haz clic con el botón derecho en la columna Item # y divídela en dos seleccionando Dividir columna → Por delimitador. En el cuadro de diálogo, selecciona Espacio en el desplegable y haz clic en Aceptar. Una vez más, este proceso ofrece una mayor facilidad de uso y una gama más amplia de funciones en comparación con la función tradicional de Texto a Columnas.

Las columnas delimitadas se etiquetan inicialmente como Item #.1 y Item #.2. Para cambiarles el nombre, basta con hacer doble clic en las cabeceras de las columnas en el Editor. Como ocurre con todas las modificaciones en Power Query, estas alteraciones se registran mediante Pasos Aplicados, lo que permite revertirlas o ajustarlas sin esfuerzo cuando sea necesario.

Cambiar tipos de datos

En Power Query, a cada columna se le asigna un tipo de datos específico, que define las operaciones que se pueden realizar con ella. Al importar un conjunto de datos, Power Query intenta determinar automáticamente el tipo de datos más adecuado para cada columna. Sin embargo, hay situaciones en las que esta detección automática puede mejorarse o ajustarse.

Por ejemplo, tomemos la columna UPC. Por defecto, se le asigna el tipo de datos Número entero. Sin embargo, como no prevemos realizar operaciones matemáticas significativas en esta columna, es más adecuado almacenarla como texto. Para ello, haz clic en el icono de número situado junto a la columna UPC y cambia su tipo de datos a Texto, como se ve en la Figura 4-3.

Procede con los siguientes cambios de tipo de datos:

• Convierte la columna ISBN 13 en Texto.

• Convierte la columna Retail a Moneda.

Figura 4-3. Cambiar los tipos de datos de las columnas en Power Query

Borrar columnas

Eliminar columnas innecesarias de un conjunto de datos facilita su procesamiento y análisis. Selecciona la columna BTkey y pulsa la tecla Supr para eliminarla de tu consulta. Si decides incluir esta columna más adelante, puedes recuperarla fácilmente a través de la lista Pasos aplicados, como se explica en el Capítulo 2.

Trabajar con fechas

Power Query ofrece una amplia gama de sofisticados métodos para gestionar, transformar y dar formato a las fechas. Facilita la modificación de los tipos de fecha, permitiendo a los usuarios extraer componentes como el número de mes y el nombre del día, y almacenarlos después en los tipos de datos más adecuados.

Para explorar esta funcionalidad, vamos a aplicarla a la columna Release Date de varias formas diferentes. Empieza creando copias de esta columna: haz clic con el botón derecho en la columna y elige "Duplicar columna". Realiza esta operación dos veces más para generar un total de tres columnas de fecha duplicadas.

Haz clic con el botón derecho del ratón en la primera columna duplicada de Release Date y navega hasta Transformar → Año → Año, como en la Figura 4-4. La columna se reformateará y su tipo cambiará para mostrar sólo el año en lugar de la fecha completa.

Figura 4-4. Transformación de columnas de fecha en Power Query

Extrae los números de mes y día de las dos columnas siguientes. Haz doble clic en las cabeceras de las columnas y renómbralas como Year, Month, y Day, respectivamente, para reflejar los datos reformateados. Cierra y carga tus resultados en una tabla de Excel.

Buen trabajo al ejecutar con éxito una serie de manipulaciones de datos orientadas a columnas en Power Query. Ya estás listo para cargar esta consulta en Excel.

Crear columnas personalizadas

Añadir una columna calculada es una tarea habitual en la limpieza de datos. Ya sea un margen de beneficio, la duración de una fecha u otra cosa, Power Query se encarga de este proceso a través de su lenguaje de programación M.

Para la siguiente demostración, dirígete a la hoja de cálculo teams de ch_04.xlsx. Este conjunto de datos incluye los registros de temporada de cada equipo de las Grandes Ligas de Béisbol desde el año 2000. Nuestro objetivo es crear una nueva columna que calcule el récord de victorias de cada equipo durante la temporada. Este cálculo se realiza dividiendo el número de victorias por el número total de victorias y derrotas combinadas.

El primer paso, por supuesto, es cargar los datos en Power Query. A continuación, en la cinta del Editor, dirígete a Añadir columna → Columna personalizada. Nombra tu columna personalizada Wpct y defínela utilizando la siguiente fórmula:

[W] / ([W] + [L])

El lenguaje de programación M de Power Query sigue una sintaxis parecida a las tablas de Excel, en la que las referencias a columnas se encierran entre corchetes simples. Aprovecha el IntelliSense de Microsoft para pulsar el tabulador y completar automáticamente el código mientras escribes estas referencias. Además, tienes la opción de hacer doble clic en las columnas deseadas de la lista "Columnas disponibles", lo que las insertará en el área de fórmulas.

Si todo es correcto, aparecerá una marca de verificación verde en la parte inferior del cuadro de diálogo, indicando que no se han detectado errores de sintaxis, como se muestra en la Figura 4-5.

Figura 4-5. Crear un cálculo de porcentaje ganador

Una vez que hayas creado esta columna, sigue adelante y cambia su tipo de datos en Power Query a Porcentaje.

Cargar e inspeccionar los datos

Nuestra nueva columna está calculada y lista para trabajar con ella. En la cinta del Editor de Power Query, dirígete a Inicio → Cerrar y cargar → Cerrar y cargar en, luego selecciona Informe de tabla dinámica y Aceptar. A partir de ahí, puedes analizar los datos, como calcular la media de Wpct para cada nombre de equipo, como se muestra en la Figura 4-6.

Figura 4-6. Resumir los resultados en una tabla dinámica

Tipos de datos de Power Query frente al formato de celda de Excel

Ten en cuenta en que alterar el tipo de datos de una columna en Power Query sólo modifica cómo se almacenan en el propio Power Query; no cambia el formato frontal de los datos en Excel. Por ejemplo, si formateas una columna como porcentaje en Power Query, como Wpct en el ejemplo anterior, aún tendrás que aplicar el formato de porcentaje en el propio Excel. En la Parte III, exploraremos cómo cambiar sistemáticamente la presentación de los datos mediante el formato de datos en Power Pivot.

Columnas calculadas frente a medidas

En es importante señalar que la media Wpct que aparece en la Tabla dinámica es una media simple, no ponderada, de los porcentajes de victorias de la temporada. Esto significa que las temporadas con un menor número de partidos -como la temporada 2020, afectada por la pandemia- tienen un impacto desproporcionado en el cálculo. Para comprobarlo, compara el valor Average of Wpct de la Tabla dinámica con nuestro propio cálculo de Excel, como se muestra en la Figura 4-7.

Figura 4-7. Aparente error de cálculo de la tabla dinámica

Para resolver este problema, un enfoque consiste en utilizar medidas dinámicas para realizar agregaciones y cálculos en tiempo real adaptados al contexto de análisis. Esto se consigue con herramientas como el Modelo de Datos de Power Pivot y el lenguaje DAX, tratados en la Parte II de este libro.

Esto no significa que deban evitarse por completo las columnas calculadas en Power Query. Son sencillas de crear y computacionalmente eficientes. No obstante, si existe la posibilidad de que estas columnas den lugar a agregaciones engañosas, es aconsejable optar en su lugar por una medida DAX .

Dar nueva forma a los datos

En el Capítulo 1, se te presentó el concepto de datos "ordenados", en los que cada variable se almacena en una y sólo una columna. Puede que recuerdes los datos de la hoja de cálculo sales como un ejemplo de datos desordenados. Afortunadamente, Power Query resuelve este problema crítico de almacenamiento de datos. Para empezar, ve a la conocida hoja de trabajo sales del libro de trabajo ch_04.xlsx. Carga esta tabla en Power Query para iniciar elproceso de transformación de datos.

El objetivo es "despivotar" o "fundir" todas las columnas de ventas en una columna llamada sales, junto con las etiquetas de esas ventas en una columna llamada department. Para ello, mantén pulsada la tecla Ctrl y selecciona las tres primeras variables: customer_id, channel, y region. Haz clic con el botón derecho y selecciona Unpivotar otras columnas, como se muestra en la Figura 4-8.

Figura 4-8. Despivotar un conjunto de datos en Power Query

Por defecto, las dos columnas sin divisiones resultantes se llamarán Attribute y Value. Cámbiales el nombre a department y sales, respectivamente. Ahora puedes cargar la consulta en una Tabla dinámica y analizar las ventas por canal y región. Los resultados y ventajas de crear una Tabla dinámica basada en estos datos reestructurados se ven en la Figura 4-9.

Figura 4-9. Operar en un conjunto de datos no pivotado con TablasPivotantes

Conclusión

En este capítulo se han explorado distintas formas de manipular columnas en Power Query. El Capítulo 5 da un paso más al trabajar con varios conjuntos de datos en una sola consulta. Aprenderás a fusionar y anexar fuentes de datos, así como a conectar con fuentes externas como archivos .csv.

Ejercicios

Practica la transformación de columnas en Power Query con el archivo ch_04_exercises.xlsx que se encuentra en la carpeta exercises\ch_04_exercises del repositorio complementario del libro. Realiza las siguientes transformaciones en este conjunto de datos de órdenes de trabajo:

1. Transforma la columna date a un formato de mes, como cambiar 1/1/2023 a Enero.

2. Transforma la columna owner al caso adecuado.

3. Divide la columna location en dos columnas separadas: zip y state.

4. Remodela el conjunto de datos de forma que subscription_cost, support_cost, y services_cost se consoliden en dos columnas: category y cost.

5. Introduce una nueva columna llamada tax que calcula el 7% de los valores de la columna cost.

6. Convierte la variable zip al tipo de datos Texto, y actualiza las columnas cost y tax a Moneda.

7. Carga los resultados en una tabla.

Para las soluciones a estas transformaciones, consulta ch_04_solutions.xlsx en la misma carpeta .