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
 600
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.

  • Kevin Julian Horacio Sotelo

    Hola Miguel, antes que nada quiero agradecerte por existir 🙂

    Yo necesito hacer exactamente lo mismo que se muestra aca, pero hecho por hora, por ejemplo:

    01/01/2014 07:00:00 AM
    01/01/2014 08:00:00 AM
    …..
    …..
    …..
    Hasta la fecha / hora actual, Imagino que al comenzar en vez de ingresar List.Date seria por ejemplo List.DateTime o algo parecido.., pero no encuentro ninguna lista de las funciones que existen para power query.

    Mil gracias!

    • Hola Kevin!

      Gracias a ti por existir 🙂

      La mayoría de las veces en este tipo de situaciones se recomienda tener una tabla Calendario y otra tabla de Tiempo que puede ir ya sea por intervalos de tiempo (10, 15, 30, o 60 minutos) o simplemente a nivel de minuto.

      Si solamente necesitas una tabla con la combinación de Fecha y Hora en intervalos de 60 minutos entonces el siguiente código te debe de funcionar:
      = List.DateTimes(Origen, (Number.From(DateTime.LocalNow())- Number.From(Origen))*24 ,#duration(0,1,0,0))

      el paso “Origen” lo tienes que configurar como #datetime(año, mes, día, hora, minutos, segundos)

      Saludos!

      • Kevin Julian Horacio Sotelo

        Buenísimo me re sirvió, cuantas horas frente al monitor me ahorraste!!.

        muchas gracias,

        • Kevin Julian Horacio Sotelo

          Solo me queda un problema por resolver, tengo una tabla con fechas, y otra tabla con fecha/hora. Hasta ahí vengo perfecto.
          Lo que necesito es agregarle a la tabla fecha/hora a que fecha comercial (o fecha de producción) pertenecen los registros.

          por ejemplo: una empresa que trabaje de:
          “01/01/2017 10:00:00 AM” a
          “02/01/2017 06:00:00 AM”

          Tiene como fecha comercial o de producción 01/01/2017, y el día 02/01/2017 comienza a las 06:00:00 AM del mismo dia.

          Espero haber sido claro y gracias de antemano por responder.

          • Hola Kevin, creo que sería mejor que publicaras tu escenario en el foro oficial de Power BI:
            https://community.powerbi.com/t5/Desktop/bd-p/power-bi-designer

            con datos de ejemplo Origen y también una tabla que demuestre cómo debe de verse el resultado junto con una breve explicación de la lógica para llegar a dicho resultado. Una vez lo publiques, te pido por favor nos compartas el enlace acá.

            Al publicarlo en el foro ayudas a más personas que puedan estar pasando por el mismo escenario (da mayor visibilidad) y también existen otras personas de la comunidad que puedan ayudarte.

            Saludos,

          • Kevin Julian Horacio Sotelo

            Pude solucionarlo con DAX, agregando una columna calculada =Fecha/Hora – (06:00:00).

            No se si vale esta solución en una pagina exclusiva de PowerQuery, pero para salir del apura funciona bien!!

          • Excelente, Kevin! me alegra que lo solucionaras

  • Erika

    Hola amigo soy nuevo en Power query me gustaría que me ayuden como haría para crear un calendario que me muestre fin.mes (31/01/2005) cómo se hace en Excel con su columna años desde el 2005 hasta 2017 como lo haría ayuden por qué quiero vincular todos los fin de mes que muestra un sistema que está hecho de esa manera