Tabla Calendario Dinámica

El caso

Cuando se enfrentan a un escenario en el que es necesario crear una Dimensión de Fecha o básicamente lo que llamamos una tabla de calendario, que tome en consideración que podríamos crear dicha tabla en Excel y utilizarla más adelante en nuestros escenarios de inteligencia de tiempo con Power Query o Power Pivot. La mayoría de las veces, esta dimensión de Fecha sería demasiado larga y contendría fechas que no son relevantes para el informe actual. (Al igual que data del año 2020, por ejemplo, y sólo tenemos datos hasta el año 2015; eso se traduce en 5 años de vacío o residuos en nuestro informe)

Hay otros escenarios en los que Simplemente queremos una tabla calendario agradable con etiquetas de texto u otros formatos de la utilización de su cultura específica (español, portugués y tal)

Este patrón intentará mostrar cómo crear una tabla de calendario, cómo utilizar algunas de las funciones de fecha encontradas en Power Query, y lo fácil que es trabajar con Power Query.

Descargar el Archivo

Paso 1: Crear la consulta y Agregar la Fecha de Inicio

Antes de agregar cualquier tipo de código, necesitamos crear la consulta. Para ello, iremos a Power Query ->  De otras fuentes -> Consulta en blanco. Esto lanzará el editor de consultas de Power Query y dentro de dicha ventana podemos agregar nuestro propio código dentro de la barra de fórmulas. Dentro de dicha barra insertamos el siguiente código: =#date(2013,1,1) DateLiteralNotarás algo diferente en este código y es que utilizamos algo llamado literal al usar #date(2013,1,1).

Lo que hicimos es sencillo – agregamos el valor de la fecha. 2013 es el valor del año de la fecha, el segundo parámetro es para el valor del mes y el tercero es para el valor del día. Agregamos esto en nuestro primer paso. El nombre de dicho paso es Origen y contiene el valor de la fecha de inicio de nuestra tabla calendario.

Paso 2: El código que hace toda la Magia

Vayamos y creamos un nuevo paso personalizado al dar clic en el icono FX en la barra de fórmulas. Esto agregará un nuevo paso llamado Personalizada que hace referencia al paso anterior. Nos gustaría cambiar el código original de ese paso para que sea el siguiente:

= List.Dates(Origen, Number.From(DateTime.LocalNow())- Number.From(Origen) ,#duration(1,0,0,0))  

He aquí el desglose de la fórmula:

  • List.Dates – nos ayuda a crear la lista de fechas que necesitamos y sus entradas son
    • La primera fecha de la tabla
    • El número de fechas a devolver
    • El incremento entre fechas
  • Origen – como ya sabes, este es el valor de la fecha de inicio por lo que va dentro del primer parámetro
  • Number.From – utilizamos esta función múltiples veces para transformar cualquier valor a un número y luego hacer operaciones matemáticas
  • DateTime.LocalNow – es el equivalente de AHORA() en Power Query por lo que devuelve la fecha y hora actual. Esto nos ayudará para mantener el rango de fechas hasta el presente día
  • #duration(1,0,0,0) – otro literal que agregar 1 día (para que el incremento entre fechas sea de 1 día a la vez)

Esta fórmula devuelve entonces una lista de Fechas como puedes ver en la siguiente imagenFormulaMagic Ve adelante y convierte esta lista a una tabla utilizando las opciones dentro de la cinta de herramientas de lista. Luego de hacer la conversión a tabla, cambia el tipo de datos de la columna a tipo fecha y dale un nuevo nombre a dicha columna para sea más descriptiva. Algo como Fecha.

Paso 3: Agregando las columnas basadas en Números y Fechas (usando la interfaz de usuario)

El próximo paso es agregar las columnas como Número de Mes, Número de Año, Fecha comienzo del mes, última fecha del mes y otras. Hacemos simplemente siguiendo estos pasos (por favor asegurarse de seleccionar la columna de fechas antes de hacer estos pasos):

  1. Ve a la cinta de Agregar columna
  2. Dentro del grupo De Fecha y Tiempo
  3. Selecciona la nueva columna que deseas agregar de la lista desplegable

He aquí cómo luciría el resultado:

Paso 4: Las columnas basadas en Texto (y la opción de cultura)

Este es el último paso y agregaremos otras columnas personalizadas mediante la cinta de Agregar columna -> Agregar columna personalizada.

Luego, agregaremos estas nuevas columnas insertando una fórmula dentro de la ventana para crear columnas personalizadas. Estas son las fórmulas que utilizaremos:

Columnas de nombre de Mes = Date.ToText([Date], "MMM", "es-419")

Columna de nombre de Día de Semana = Date.ToText([Date], "ddd", "es-419")

Verás que utilizamos la misma función en las dos columnas. El último parámetro de dicha función es un valor de texto, “es-419”, que utilizamos para poder obtener resultados en español. Podrías cambiar este valor utilizando cualquier que se muestra en el siguiente enlace National Language Support (NLS) API Reference y utilizar el valor para otra cultura. Este último parámetro es opcional y si no lo utilizamos el resultado será calculado utilizando la cultura estadounidense (inglés) (en-US).

El resultado final lucirá de la siguiente manera:

Conclusión

Ahora conocemos cómo crear una simple Dimensión de Fechas o Tabla calendario y cómo la interfaz de usuario trabaja para poder crear nuevas columnas de fechas. Esto muestra realmente cuan extensible y fácil de usar es Power Query.

Patrones para Power Query
Taller de Power Query

este trabajo ha sido compartido 0 veces
 500
Sobre

 Power Query Training

  (13 Artículos)

Somos un equipo comprometido en ofrecer el mejor contenido para los nuevos y experimentados usuarios de una herramienta en la que creemos: Power Query. Estamos firmemente convencidos de que esta es la mejor herramienta que ha sido agregada a Microsoft Excel en un largo tiempo.

  • Pedro Diaz Galvez

    Buen día, realicé los pasos, eliminando los espacios en las fórmulas para que lo identifique.Muchas gracias por la ayuda, mi consulta es :”qué hago si quiero fechas hasta el 2020 por ejemplo, porque esta tabla la quiero usar en un modelo de Power Pivot y resultaría complicado estar entrando a la tabla de fechas solo para actualizar diario dado que la última fecha es el dia “hoy” del sistema, Muchas gracias- Enrique Díaz

    • Hola Pedro,
      La fórmula que mencionamos en este artículo es dinámica, por lo que siempre actualizará la última fecha. Cada vez que refresques el modelo de datos se estaría calculando nuevamente, por lo que no deberías de preocuparte por actualizar ninguna fecha ni tampoco por entrar dentro de la ventana de Power Query.
      No recomendamos utilizar tablas con fechas que no son necesarias, pues pueden traer complicaciones a la hora de hacer inteligencia de tiempo con DAX. Si deseas utilizar esta tabla para Power Pivot entonces te recomendamos reemplazar la fórmula:

      = List.Dates(Origen, Number.From(DateTime.LocalNow())- Number.From(Origen) ,#duration(1,0,0,0))

      por
      = List.Dates(Origen, Number.From(Date.EndOfYear(DateTime.LocalNow()))-Number.From(Origen), #duration(1,0,0,0))

      Si mas allá de esto deseas todavía utilizar una fecha exacta como último registro de tu tabla calendario entonces necesitarás utilizar una fórmula como la siguiente:
      = List.Dates(Origen, Number.From(#date(2020,12,31)- Number.From(Origen) ,#duration(1,0,0,0))
      donde estás definiendo cuál será tú última fecha.

      Saludos,

      • Pedro Diaz Galvez

        Miguel, al final reemplacé esta parte de la fórmula “Number.From(DateTime.LocalNow())- Number.From(Origen)” por un número que calza con la fecha 31-Dic-2020. Mil gracias por su valioso trabajo me ha sido de mucha utilidad.