Crear un calendario con Power Query

Supongamos que queremos crear un calendario con Power Query (¿por qué queríamos hacer semejante cosa? Bien, hay varios motivos pero no son el tema de este post).

Aparentemente la tarea es bien sencilla. Power Query tiene un "atajo" para crear Listas: {x..y} donde x e y son números enteros. Como sabemos, las fechas están representadas por números enteros de manera que si sabemos que el 01/01/2020 es el número 43831 y el 44196 el 31/12/2020, hacemos lo siguiente:

1 - abrimos una consulta en blanco

2 - en la barra de las fórmulas escribimos ={43831..44196} lo cual crea un serie aritmética de razón 1

3 - Convertimos la lista en tabla con Transformar-Convertir-A la tabla (¿quién tradujo esto?) y e luego cambiamos el tipo de dato a fecha

El problema con este método es la falta de dinamismo. Podemos crear un modelo dinámico de esta manera:

1 - en una celda introducimos el año para el cual queremos crear el calendario y creamos un nombre definido que se refiera a esa celda (que llamaré year en mi ejemplo)

2 - creamos una consulta a esta celda (el estar ligada a un nombre definido, Power Query la trata como tabla), hacemos drill-down" (como mostré en este post) y guardamos la consulta como Solo conexión (de hecho estamos creando un parámetro).

3 - Abrimos una consulta en blanco, activamos el Editor Avanzado y ponemos este código

 let  
   empieza = Number.From(#date(year,1,1)),  
   termina = Number.From(#date(year,12,31)),  
   Origen = {empieza..termina}  
 in  
   Origen  

lo que crea esta una lista como en el caso anterior. A partir de aquí aplicamos los mismos pasos para transformar la Lista en Tabla.
El método {..} no acepta fechas para crear la lista por lo que usamos la función Number.From para convertir las fechas en números enteros.

De esta manera basta con cambiar el año en la celda year y apretar Actualizar todo para obtener el nuevo calendario.

Power Query nos permite crear un calendario también con la función List.Dates. Esta función tiene tres argumentos;

1. la fecha inicial (start),
2. la cantidad de días a considerar (count),
3. el "paso" (step), es decir, el lapso entre cada fecha.

Si creamos un calendario para todo el año, se nos crea el problema de determinar si el año es bisiesto. En ese caso el step es 366; caso contrario 365 (con el método anterior no tenemos este problema ya que proveemos la fecha de inicio y de cierre del calendario).

Podemos resolver el problema usando la función Day.IsLeapYear. Esta función da true si el año es bisiesto y falso  en caso contrario. Así que la podemos usar para crear un condicional para determinar la cantidad de días en el año. Este es el código de la consulta

 let  
   empieza = #date(year,1,1),  
   dias = if Date.IsLeapYear(empieza) then 366 else 365,  
   Origen = List.Dates(empieza,dias,#duration(1,0,0,0)),  
   #"Convertida en tabla" = Table.FromList(Origen, Splitter.SplitByNothing(), null, null, ExtraValues.Error),  
   #"Tipo cambiado" = Table.TransformColumnTypes(#"Convertida en tabla",{{"Column1", type date}}),  
   #"Columnas con nombre cambiado" = Table.RenameColumns(#"Tipo cambiado",{{"Column1", "Fechas"}})  
 in  
   #"Columnas con nombre cambiado"  

En la segunda línea del código creamos la variable dias que luego usamos en la función List.Dates.

Actualización: quien esté interesado puede descargar el archivo aquí.


En el próximo post veremos como crear un lista de fechas y horas, tarea un poco más complicada.