Calendario perpetuo de vacaciones – POTW #12 (Power Query Solution)

En esta publicación de blog, exploraremos mi solución al . Para este desafío, volvimos a visitar el desafío inaugural del Problema de la semana de LuckyTemplates, donde creamos un calendario perpetuo de vacaciones a partir de una combinación de fechas específicas y relativas. Pero esta vez, usaremos solo Power Query . Puedes ver el video completo de este tutorial en la parte inferior de este blog.

Si está atascado en el desglose del problema en sí, puede volver a visitar la para ver si hay elementos en alguno de los enfoques que puedan ayudarlo con este desafío.

Al leer los envíos, he visto una combinación de personas que aprovechan la interfaz de usuario y escriben funciones personalizadas para crear un calendario perpetuo. Ha sido agradable analizar cada entrada, y si quieres inspirarte, te animo a que hagas lo mismo.

Cuando yo mismo estaba trabajando en este escenario, terminé creando múltiples soluciones. El que yo elegí es el que mejor ilustra cómo no sólo se puede dividir un problema en partes más pequeñas , sino también hacerlas visibles . Esto es muy útil si encuentra un error o resultados inesperados.

Tabla de contenido

• El punto de partida para crear un calendario perpetuo
• Recuperar los años de la tabla de fechas
• Obtener el número de la columna del día
• Invocación de la función personalizada para el calendario perpetuo de días festivos
• Crear una lista de fechas
• Aplicar la lógica condicional
• Revisión del código M para el calendario perpetuo de días festivos
• Conclusión

El punto de partida para crear un calendario perpetuo

Esta mesa navideña cruda es nuestro punto de partida. Para armar una fecha, necesitamos un año, un mes y un día para fechas específicas. Todo lo que falta es el año.

Pero para fechas relativas, necesitamos hacer un poco más de trabajo. Para empezar, debemos dividir esa columna de día en el número y el día de la semana. Repasemos esos pasos.

Recuperar los años de la tabla de fechas

Para hacer que esta solución sea dinámica, recuperaré los años de la tabla de fechas . En la barra de fórmulas, puede ver que he usado una proyección para obtener una tabla de una sola columna de mi tabla de fechas. Esto, por supuesto, contendrá duplicados. Para eliminarlos, agregué Table.Distinct .

Cuando hago clic en el lado en el espacio en blanco al lado de cualquiera de estas tablas, también verá una lista con los distintos años presentes en mi consulta de tabla de fechas.

Nos dieron un nombre de mes , pero en realidad necesitaremos un número de mes . Ahora, cuando concatenamos un año con el nombre de un mes, podemos usar la función Date.From para devolver el primero de ese mes como valor de fecha.

Al agregar Date.Month , nos queda solo el número de mes para esa fecha específica. Para enero, devuelve un 1. Para febrero, devuelve un 2, y así sucesivamente.

Obtener el número de la columna del día

Para obtener el número de la columna del día, decidimos no dividir la columna y extraer el valor en su lugar. Como puede ver en la barra de fórmulas, usamos Text.BeforeDelimiter y usamos ese espacio para identificar la parte derecha e izquierda de esa cadena de texto.

Si no hay espacio, queremos devolver lo que quede en la columna del día. Entonces, para el primer registro, devuelve un 1. Para el segundo registro que contiene ese espacio, devuelve la primera parte de la cadena de texto, la palabra Tercero .

Usaremos un enfoque similar para obtener el nombre de la fecha, pero usaremos la función List.PositionOf . Esto devuelve el desplazamiento basado en cero del valor en la lista pasada como primer argumento y devuelve -1 si el valor no aparece en esa lista.

Para el primer registro, pasa el valor 1, que no está presente en nuestra lista. Es por eso que devuelve el valor de -1. Para el segundo registro, devuelve el valor Lunes .

Pasa eso a nuestra función y devuelve un 1 porque es el segundo elemento de nuestra lista. A continuación, reemplazaremos todos los valores -1 con un valor nulo .

En este punto, hemos creado todos los componentes básicos que necesitamos para invocar la función personalizada.

Invocación de la función personalizada para el calendario perpetuo de días festivos

En la barra de fórmulas, podemos ver que la función toma cuatro argumentos: un año , un mes , un número y un día de la semana .

Pero, ¿cómo determina ese valor de fecha? Usaremos List.PositionOf nuevamente para obtener un valor numérico para el numeral. Tenemos una lista que contiene primero, segundo, tercero y cuarto. Cuando pasamos el número a List.PositionOf , devuelve el desplazamiento basado en cero.

Nuevamente, si un valor no está presente, devuelve un -1. Entonces, para el primer registro, estamos pasando el valor 1. 1 no está presente en esta lista, por lo que devuelve el valor de -1. Para el segundo registro, estamos pasando el valor Tercero, que es el tercer elemento de nuestra lista. Esto devuelve el valor de 2.

Crear una lista de fechas

Ahora vamos a crear una lista de fechas. Determinaremos la fecha de inicio de ese mes, determinaremos la cantidad de días de ese mes y usaremos List.Dates para crear una lista que contenga todos los días de ese mes.

A continuación, usaremos List.Select para conservar solo las fechas que se encuentran en un día de la semana específico.

Cuando hacemos clic en el lado al lado de cualquiera de estas listas, podemos ver que solo contiene estos cuatro elementos.

Aplicar la lógica condicional

Finalmente, aplicaremos la lógica condicional para construir o recuperar una fecha. Si el día de la semana es nulo , lo que indica que tenemos una fecha específica, podemos usar la #fecha intrínseca para construir un valor de fecha.

Si el número es igual al último, entonces podemos usar la función List.Reverse para invertir el orden en la lista y extraer el primer valor.

Si queremos cambiar este orden ascendente a descendente, siempre podemos extraer la última fecha de ese rango de fechas. No importa si tenemos cuatro o cinco elementos en la lista. Invirtiendo el orden y extrayendo el primer artículo, siempre tenemos la última fecha.

Para todos los demás casos, usamos List.Skip .

Podemos extraer el valor en función de esta posición en la lista. Para el día de Acción de Gracias, el número es cuarto y cuarto devuelve un 3. Esto omitirá los tres primeros valores de la lista y siempre devolverá el cuarto elemento.

Pongamos todo junto y vayamos a la consulta de solución. Para el paso de origen, estamos llamando a la tabla de fechas de vacaciones RAW. A continuación, agregaremos una columna que devuelva una lista con fechas.

Revisión del código M para el calendario perpetuo de días festivos

Revisemos el código M para la columna. Para cada fila de la tabla, anidamos una tabla que contiene los años distintos de la tabla de fechas. Luego transformamos los valores en la columna de ese año llamando a la función personalizada.

Por supuesto, para poder invocar la función personalizada, primero tenemos que hacer alguna configuración. Tenemos que obtener el número del mes , el valor numérico y, por supuesto, el día de la semana . Luego, de esa tabla anidada, mantuvimos la lista con fechas.

Cuando hacemos clic en el lado del espacio en blanco, podemos ver una vista previa de esa lista a continuación.

El siguiente paso es expandir esa lista y agregar CelebratedOnDate .

Esta es la lógica que usamos en este cálculo: si la fecha es un sábado, vamos a restar un día para que llegue un viernes. Si es domingo, vamos a agregar un día para que aterrice en lunes. Si no es sábado o domingo, entonces queremos un nulo.

Finalmente, el último paso es cambiar los tipos.

Conclusión

Solo un recordatorio rápido de que es una serie continua que ocurre cada 1er y 3er miércoles en el foro LuckyTemplates. El problema publicado en la primera semana requiere una solución basada en DAX, mientras que el problema de la tercera semana solicita una solución basada en Power Query.

Si disfrutó del contenido cubierto en este POTW, suscríbase al canal de televisión LuckyTemplates para obtener más información. Tenemos una gran cantidad de contenido que sale todo el tiempo de mí y una variedad de creadores de contenido, todos dedicados a mejorar la forma en que usa LuckyTemplates y Power Platform.

Toronjil