Convertir el diseño de tipo de calendario en formato tabular en LuckyTemplates mediante el editor de consultas

Analizar los datos que tenemos puede ser complicado si no está correctamente ordenado. En este tutorial, explicaré cómo transformar datos repetidos verticales y horizontales en un formato tabular. Hacemos esto para que nos resulte más fácil analizarlo con LuckyTemplates. Puede ver el video completo de este tutorial en la parte inferior de este blog.

Este tutorial se realizó para abordar la pregunta de un  miembro  . El miembro solicitó una forma en la que pudiera remodelar sus datos de un diseño tipo calendario a un formato tabular.

Antes de comenzar, primero examinemos los datos que tenemos.

Aquí, tenemos un diseño de datos en el que los días se configuran horizontalmente y las semanas se configuran verticalmente. Este tipo de disposición nos resulta familiar a la mayoría de nosotros, ya que es la forma en que podemos comprender fácilmente los datos.

Sin embargo, este tipo de diseño es más difícil de analizar ya que hay muchas columnas y filas a tener en cuenta.

Dicho esto, transformemos los datos en un formato de tabla adecuado donde terminemos con una columna para las fechas, una columna para los movimientos y otra columna para los nombres.

Tabla de contenido

• Limpieza de datos para un formato tabular adecuado
• rompiendo las mesas
• Identificación de la lista para las nuevas tablas para un formato tabular adecuado
• Determinar qué valores pertenecen a la tabla
• Agrupación de los datos para un formato tabular adecuado
• Anexando las tablas separadas
• Conclusión

Limpieza de datos para un formato tabular adecuado

Para facilitar las cosas, transformemos nuestros datos dentro del Power Query Editor . Pero antes de hacerlo, asegurémonos de que la barra de fórmulas esté visible en su pantalla. Si no es así, vaya a la pestaña Ver y luego marque la casilla junto a la barra de fórmulas.

El primer paso es eliminar los valores innecesarios en nuestros datos. Eliminemos la primera columna y las dos filas superiores de nuestros datos, ya que en realidad no los necesitamos. Para hacer eso, haga clic en la pestaña Eliminar filas, haga clic en Eliminar filas superiores, ingrese 2, luego presione Aceptar.

Y ahora, para eliminar la primera columna, vaya a la pestaña Elegir columnas y luego anule la selección de la columna 1 desmarcando la casilla junto a ella.

También eliminemos las filas del índice 14 a 16, ya que están completamente llenas solo con valores nulos. Nuevamente, vaya a la pestaña Eliminar filas y luego haga clic en Eliminar filas en blanco .

Otro problema que tenemos es que los valores de fecha saltan una columna cada vez. Las fechas siempre van seguidas de un valor nulo, como se destaca a continuación. Esto sucede no solo en la fila superior sino también en las filas inferiores.

Para llenar estos vacíos, primero transpongamos nuestra tabla de datos yendo a la  pestaña Transformar y luego haciendo clic en el botón Transponer.

La mesa ahora debería verse como si hubiera sido volteada de lado después de realizar la acción.

A continuación, seleccione todas las columnas que contienen las fechas, haga clic con el botón derecho en el encabezado de la columna y luego seleccione Rellenar hacia abajo.

Esto debería duplicar las fechas y llenar los espacios en blanco en las columnas seleccionadas.

rompiendo las mesas

Mirando los datos que tenemos ahora, vemos que hay cuatro tablas adyacentes entre sí. Las columnas 1 a 7 forman una tabla, las columnas 8 a 13 forman otra tabla y así sucesivamente.

Para agregarlos en una sola tabla, debemos realizar una operación básica de no pivotar para transformarla en un formato tabular adecuado. Pero dado que las mesas están adyacentes entre sí, no podemos simplemente hacer eso. Tenemos que separar esta mesa y unirlas de nuevo en mesas más pequeñas.

Para hacer eso, crearemos la primera fórmula para este tutorial. Hagamos clic en el botón fx y luego agreguemos la fórmula que vamos a usar. Vamos a utilizar la operación Table.ToColumns . Esto crea una lista de listas anidadas de valores de columna de una tabla. Cada columna se convertirá en una lista.

Así es como he configurado la fórmula.

La fórmula crea una lista de las columnas que tenemos previamente. El primer valor de la lista contiene la columna 1, el segundo valor contiene la columna 2 y así sucesivamente.

Identificación de la lista para las nuevas tablas para un formato tabular adecuado

Ahora, debemos identificar qué valores en la lista contienen valores de fecha. Una vez que sabemos dónde se encuentran estas fechas, podemos determinar dónde comienza o termina cada una de nuestras tablas separadas.

Para hacerlo, primero volvamos a convertir la lista en una tabla haciendo clic en el botón A la tabla en la esquina superior izquierda.

Luego agregue una columna de índice al lado.

Lo siguiente que vamos a hacer es agregar otra columna al lado de la columna de índice. Esta columna debería ayudarnos a determinar qué valores en Column1 contienen los valores de fecha. Llamemos a esta columna Index2.

Para hacer eso, vamos a incorporar una fórmula en Index2, que devuelve el valor de índice de Column1 si contiene una fecha.

Vamos a usar la y luego haremos referencia a Column1. Los corchetes servirán como operador; agregar 0 entre estos corchetes significa que obtenemos el primer valor de la lista; entonces determinamos si es una fecha. Si es así, quiero que la fórmula devuelva el número de índice; si no es así, no quiero que devuelva ningún valor.

Dado que el primer valor de la Columna 1 contiene una fecha, el primer valor del Índice 2 debe contener su número de índice 0. Además, dado que el segundo valor de la Columna 1 no contiene una fecha, el segundo valor del Índice 2 solo debe contener un valor nulo.

Así es como configuré la fórmula para Index2. Solo asegúrese de que no haya errores de sintaxis en su fórmula.

Después de hacer eso, nuestra columna Index2 ahora debería tener el índice de los valores en Column1 que contiene la fecha. También incluirá algunos valores nulos como resultado de la fórmula que hemos configurado.

Determinar qué valores pertenecen a la tabla

Ahora, estos valores de índice dentro de Index2 indican los inicios y finales de las tablas separadas que tenemos. Es decir, 0 marca el inicio de la primera tabla, 7 marca el final de la primera tabla y el inicio de la segunda tabla, y así sucesivamente.

Para ayudarnos a identificar qué valores en la Columna 1 pertenecen a la misma tabla, apliquemos una acción de relleno en nuestra nueva columna.

Después de realizar la acción, los valores nulos ahora deben reemplazarse por ceros, sietes, trece y veinte. Estos números indican que pertenecen a la misma tabla. Es decir, los ceros forman una tabla, los sietes forman otra tabla y así sucesivamente.

Agrupación de los datos para un formato tabular adecuado

Como ya sabemos qué valores en la Columna 1 pertenecen a la misma tabla, agrupémoslos ahora. Para ello, utilizaremos la operación Agrupar por.

Al hacer eso, ahora terminamos con solo 4 valores en nuestra columna Index2. Observe que otra columna llamada Count está al lado de nuestra columna Index2. Esta columna cuenta el número de filas en cada uno de nuestros valores en la columna Index2.

Sin embargo, esto no es realmente lo que queremos hacer. Lo que tenemos que hacer es formar una nueva tabla usando las columnas que tenemos.

Para hacerlo, usaremos la operación Table.FromColumns , haremos referencia a la columna que contiene los valores de nuestra lista, que es Column1, y luego cambiaremos el tipo de retorno a una tabla.

Es importante asegurarse de que hemos hecho referencia a la columna correcta en nuestra fórmula. Compruebe si Column1 realmente contiene los valores de las listas.

Esto une todos los valores en Column1 con un valor de Index2 de 0 en una tabla, los que tienen el valor de Index2 de 7 en otra tabla, y así sucesivamente.

Verificamos cada registro al valor 0, luego recuperamos el valor de la lista de Column1. Finalmente, unimos esas listas para formar una sola tabla usando este código M.

Tomamos las primeras 7 columnas y las unimos en una sola tabla, tomamos las siguientes columnas, las unimos a otra tabla, y así sucesivamente.

Anexando las tablas separadas

Ahora que ya las hemos agrupado, intentemos agregar todas las tablas agregando otro paso. Nuevamente, presionemos el botón fx para agregar una nueva fórmula.

La operación Table.Combine fusiona una lista de tablas y luego crea una sola tabla a partir de ellas. Aquí, vamos a hacer referencia a la columna que contiene nuestra lista de tablas, que es la columna Contar.

Ahora tenemos una tabla donde todas las tablas anidadas se agregan nuevamente en una sola tabla grande.

Después de agregar todas las tablas, ahora podemos realizar los últimos pasos para transformar nuestros datos en un formato tabular adecuado. Seleccione las dos primeras columnas de su tabla y luego haga clic en el botón Dejar de girar otras columnas en la pestaña Transformar.

Después de eso, elimine la columna de atributos ya que realmente no la necesitamos. Vaya a Elegir columnas y anule la selección de la columna de atributos.

Luego, finalmente, establezca los tipos correctos de todas las columnas y etiquételos correctamente.

Haga doble clic en el encabezado de columna de cada columna para cambiar su nombre en consecuencia.

Para la primera columna, cambie su tipo a fecha ya que contiene nuestros valores de fecha.

Luego, seleccione la segunda y la tercera columna y establezca sus tipos en texto.

¡Todo listo! Este es el aspecto que debería tener nuestro formato tabular adecuado.

Conclusión

En este tutorial, transformamos algunos datos, que se presentaron en un diseño de tipo calendario, en un formato tabular adecuado. Primero limpiamos nuestros datos, los dividimos en tablas más pequeñas, agrupamos las tablas que pertenecen juntas y luego las agregamos todas al final.

Este tutorial le ofrece muchos conocimientos sobre cómo modelar correctamente sus datos para facilitar el análisis.

Comprender los conceptos que se usaron aquí es muy importante ya que también se pueden aplicar de muchas otras maneras.

Si los conceptos aún no le resultan claros, puede que le resulte útil consultar los enlaces siguientes.

¡Disfruta trabajando en este!

Toronjil