Tabla de dimensiones en un archivo de texto: Solución Power Query

En esta publicación de blog, repasaré el problema n.º 2 del Problema de la semana en curso en el , donde discutimos cómo clasificar datos desordenados en una tabla de dimensiones limpia. Esta es la segunda iteración de esta nueva iniciativa que estamos organizando en LuckyTemplates. Me apasiona especialmente esta serie porque brinda a todos la oportunidad de practicar más de forma regular. Puedes ver el video completo de este tutorial en la parte inferior de este blog.

El primer miércoles de cada mes, hay un desafío DAX y el tercer miércoles, hay un desafío de consulta de energía .

Esta es una gran oportunidad para explorar, descubrir y aprender cosas nuevas sobre estos lenguajes que necesita aprovechar dentro de LuckyTemplates.

Encontrará una categoría en el Problema de la semana en el foro LuckyTemplates.

El enfoque principal tiene que ver con el proceso y no tanto con el resultado.

Si tiene éxito en dividir un problema en partes más pequeñas, podrá resolver cualquier problema al que se enfrente.

Melissa de Korte

Brian habló anteriormente sobre una técnica llamada depuración de patitos de goma . Si te lo perdiste, asegúrate de ver su video. Puede ayudarte cuando te quedes atascado en estos desafíos.

Primero, examinemos la tarea en cuestión. Tenemos un archivo de texto desordenado que necesitamos transformar en una tabla de dimensiones adecuada.

Ahora, cuando miro datos como estos, en su mayor parte, se tratará de la limpieza del texto, la eliminación de caracteres no deseados, el recorte, el uso de mayúsculas, etc.

Pero también necesitamos recuperar todas las filas de esta pila de datos y convertirlos en una sola línea para cada país. Lo llamo datos de pila porque todos los nombres de campo se repiten en una sola columna para cada país.

Tengo algo de experiencia en Excel y, en mi opinión, una de sus características más potentes son las tablas dinámicas .

Tabla de contenido

• tablas dinámicas en excel
• Uso de Power Query para crear una tabla de dimensiones
• Función de limpieza de texto personalizado
• Pivotar los datos
• Conclusión

tablas dinámicas en excel

Las tablas dinámicas me permiten ver los datos segmento por segmento. Según lo que coloque en la sección de la fila, una tabla dinámica condensa cada aparición de ese segmento en una sola línea.

Luego puede dividirlo aún más arrastrando los campos a la sección de la columna.

He creado un ejemplo de cómo se ve esto. Aquí tenemos prácticamente los mismos datos que teníamos en el archivo de texto.

Y en la página siguiente, he creado una tabla dinámica vacía.

Ahora, lo que no les mostré es que tengo tres columnas aquí en lugar de las dos que ya les mostré.

Voy a arrastrar mi segmento a las filas.

Dado que la Columna 1 tenía los nombres de los campos, voy a arrastrarla a la sección de la columna.

La columna 2 tenía todos los valores de campo, así que lo arrastraré a la sección de valores.

Podemos ver aquí que las tablas dinámicas no pueden manejar cadenas de texto. Los cuenta, pero muestra que tenemos un solo valor para cada campo.

Ahora echemos un vistazo a ese segmento que creé. Así que volveré a los Datos y mostraré mi columna. Puede ver que es solo un número de índice, que identifica cada uno de los bloques separados de los datos que todavía están apilados uno encima del otro.

Entonces, para mí, la transformación clave será un pivote de los datos para volver a ponerlos en ese formato tabular.

En el foro he visto otras formas de solucionar esto. Hay otras formas de llegar a un formato tabular además de usar una acción de pivote y funcionan igual de bien. Si estás interesado en eso, ve al foro y comienza a explorar.

Uso de Power Query para crear una tabla de dimensiones

Repasemos la consulta de energía y revisemos mi solución.

Personalmente, creo que la interfaz de usuario hace un gran trabajo al escribir la mayor parte del código M para nosotros. Así que tiendo a diseñar mis consultas usando la interfaz de usuario tanto como puedo.

Una vez que la consulta haga lo que necesitaba que hiciera, iré al editor avanzado y examinaré el código M para ver si puedo modificarlo. Veamos cómo se ve eso.

Este es mi grupo base que se crea utilizando la interfaz de usuario.

Puede ver que hay muchos pasos en los pasos aplicados en el lado derecho.

Ahora bien, eso en sí mismo no es un problema, pero con solo mirar estos pasos, puede ver que hay muchas transformaciones que se pueden agrupar.

Abramos el editor avanzado.

Vemos que esta consulta tiene 31 pasos.

También agregué algunos comentarios a esta misma consulta que contiene los 31 pasos, pero la dividí en secciones.

Lo primero que hice fue eliminar el paso de tipo de cambio. Sugiero crear una función personalizada para realizar todos estos pasos de transformación de texto.

Hay muchos comentarios aquí, pero solo hay dos cosas que quiero resaltar en esta publicación de blog. La primera es la función personalizada para la limpieza de texto .

El segundo es el paso pivote para convertir este tipo de datos nuevamente en un formato tabular adecuado .

Función de limpieza de texto personalizado

Volvamos a las primeras etapas de la construcción de mi consulta, donde tenía todos esos pasos agrupados para limpiar estos textos: la columna 1 y la columna fusionada.

También agregué una columna personalizada adicional. Su único propósito es desarrollar mi función de limpieza de texto personalizado . Invoqué eso en la columna fusionada.

De esta manera, no tengo que escribir la función de una sola vez, sino que la construyo gradualmente paso a paso, agregando una nueva transformación después de revisar los resultados del paso anterior.

Veamos el código M para la función de limpieza de texto.

Como puede ver, tengo varios pasos. Cuando estaba construyendo esta función de texto, iba y venía entre la consulta para ver los resultados y ver qué construir y qué corregir a continuación.

Con este resultado realicé todas las transformaciones que necesitaba. Algunas de las funciones de M que utilicé aquí fueron proporcionadas por la interfaz de usuario cuando construí mi consulta inicial, como el Texto. Función de recorte. Sin embargo, las otras funciones utilizadas no lo eran.

Si no está familiarizado con ellas, puede buscar todas las funciones M en la guía de fórmulas M en línea. Este es el enlace donde tienes que ir.

Encontrará una sección dedicada a consultas y funciones de potencia.

Si se desplaza hacia abajo, encontrará la sección sobre la función de texto y cada sección comienza con una descripción general. Hay una lista de todas las funciones de texto dentro del lenguaje Power Query y Formula M.

Si está buscando una transformación específica, puede buscarla aquí mismo.

Pivotar los datos

La segunda parte que quería resaltar al crear esta tabla de dimensiones es la rotación de los datos en sí. Echemos un vistazo más de cerca a esto también.

Empecé agregando un archivo index. Actualicé ese índice para segmentar adecuadamente los bloques de datos. Hice esto devolviendo el número de índice para cada línea donde tenía el país de texto en la columna 1, y luego rellené ese valor.

Todo lo que tenemos que hacer es pivotar los datos en sí. En la pestaña de transformación, encontrará la columna pivote. Con la Columna 1 seleccionada, haga clic en Columna dinámica.

Usará los valores de la Columna 1 como el nuevo nombre de la columna. También quiere saber dónde están los valores de esos nombres de campo. Esos están en nuestra columna fusionada.

Si Excel puede manejar valores de texto, Power Query también puede hacerlo debido a su configuración de opciones avanzadas. Todo lo que tenemos que hacer es seleccionar No agregar para que pueda manejar valores de texto.

Una vez que hacemos clic en Aceptar, podemos ver que nuestros datos han sido pivotados.

Conclusión

La imagen de arriba es el resultado final de la consulta. Espero que haya disfrutado de cómo remodelamos los datos desordenados en el archivo de texto proporcionado y los convertimos en una tabla de dimensiones limpia que es adecuada para el análisis.

Si disfrutó de esta publicación de blog, suscríbase al canal LuckyTemplates para no perderse ningún contenido nuevo.

Espero verlos a todos en futuros desafíos del Problema de la semana.

Toronjil