Tabla de fechas con Power Query

En Power BI casi siempre es necesaria una tabla maestra de fechas, no es una tarea compleja pero si que requiere de un cierto tiempo de dedicación en cada proyecto que iniciamos. Podemos recurrir a DAX para crearla, aunque lo más eficiente es hacerlo en Power Query.

Según el tipo de conjunto de datos es posible que necesitemos las fechas comprendidas entre dos rangos concretos, o puede que lo más conveniente sea tener solo las fechas de la tabla de hechos. En cualquiera de los dos casos expondré dos funciones adaptadas por mi, que por otro lado podéis encontrar en distintos sitios de la web.

Lo ideal es tener de una función que cree la tabla así como los campos necesarios de una forma automática. Para ello crearemos un nuevo query (Nuevo origen, Consulta en blanco) y pegaremos el código que especifico más abajo entrando en el «Editor avanzado», esto crea una nueva función. Renombraremos la función por ejemplo con «fnCrearTablaFechas» y solo tendremos que invocar la función y renombrar la tabla resultante por ejemplo a «Fechas».

Siguiendo el código de la función podéis ver su funcionamiento y adaptarla a vuestro gusto, ya sea el nombre de los campos o añadir o quitar columnas.

Función tomando un rango de fechas

Esta función dispone de dos parámetros de entrada: fecha inicial y fecha final que deberemos introducir/seleccionar para invocar la función de creación de tabla.

let CreaTablaFechas = (Fecha_Inicio as date, Fecha_Fin as date) as table =>
  let
    NumeroDeDias = Duration.Days(Duration.From(Fecha_Fin - Fecha_Inicio)),
    Origen = List.Dates(Fecha_Inicio,NumeroDeDias,#duration(1,0,0,0)),
    Tabla= Table.FromList(Origen, Splitter.SplitByNothing()),
    CambiATipoFecha= Table.TransformColumnTypes(Tabla,{{"Column1", type date}}),
    CambiarNombreCampo = Table.RenameColumns(CambiATipoFecha,{{"Column1", "Fecha"}}),
    InsertaAnyo = Table.AddColumn(CambiarNombreCampo, "Año", each Date.Year([Fecha])),
    InsertaTrimestre = Table.AddColumn(InsertaAnyo, "Trimestre", each Date.QuarterOfYear([Fecha])),
    InsertaMes = Table.AddColumn(InsertaTrimestre, "Mes", each Date.Month([Fecha])),
    InsertaDia = Table.AddColumn(InsertaMes, "Día", each Date.Day([Fecha])),
    InsertDiaInt = Table.AddColumn(InsertaDia, "Año-Mes-Día", each [Año] * 10000 + [Mes] * 100 + [Día]),
    InsertaNombreMes = Table.AddColumn(InsertDiaInt, "Nombre del mes tmp", each Date.ToText([Fecha], "MMMM"), type text),
    CambiaNombreMes = Table.AddColumn(InsertaNombreMes, "Nombre del mes", each Text.Upper(Text.Range([Nombre del mes tmp], 0, 1)) & Text.Range([Nombre del mes tmp], 1, Text.Length([Nombre del mes tmp])-1)),
    EliminarMesTmp = Table.RemoveColumns(CambiaNombreMes,{"Nombre del mes tmp"}),
    InsertaMesAnyo = Table.AddColumn(EliminarMesTmp, "MesAño", each (try(Text.Range([Nombre del mes],0,3)) otherwise [Nombre del mes]) & " " & Number.ToText([Año])),
    InsertaNombreTrim = Table.AddColumn(InsertaMesAnyo, "Nombre Trimestre", each "Q" & Number.ToText([Trimestre]) & " " & Number.ToText([Año])),
    InsertaDiaSemana = Table.AddColumn(InsertaNombreTrim, "Num. Dia de la semana", each Date.DayOfWeek([Fecha])),
    InsertaNombreDiaSem = Table.AddColumn(InsertaDiaSemana, "Día de la semana tmp", each Date.ToText([Fecha], "dddd"), type text),
    CambiaNombreDiaSem = Table.AddColumn(InsertaNombreDiaSem, "Día de la semana", each Text.Upper(Text.Range([Día de la semana tmp], 0, 1)) & Text.Range([Día de la semana tmp], 1, Text.Length([Día de la semana tmp])-1)),
    EliminarColTmp = Table.RemoveColumns(CambiaNombreDiaSem,{"Día de la semana tmp"}),
    OrdenMes = Table.AddColumn(EliminarColTmp, "OrdenMes", each Number.FromText(Number.ToText([Año]) & Date.ToText([Fecha], "MM")))
  in
    OrdenMes 
in
CreaTablaFechas

Función tomando el rango de fechas de la tabla de hechos

En este caso debemos introducir también dos parámetros (TablaOrigen, CampoFecha): la tabla donde tenemos el campo fecha y el nombre del campo. En el ejemplo de la imagen siguiente: Venta, Fecha.

let CreaTablaFechas = (TablaOrigen as table, CampoFecha as text) as table =>
  let
    FechaInicio = List.Min(Table.Column(TablaOrigen, CampoFecha)),
    FechaFin = List.Max(Table.Column(TablaOrigen, CampoFecha)),
    NumeroDeDias = Duration.Days(Duration.From(FechaFin - FechaInicio)),
    Origen = List.Dates(FechaInicio,NumeroDeDias,#duration(1,0,0,0)),
    Tabla= Table.FromList(Origen, Splitter.SplitByNothing()),
    CambiATipoFecha= Table.TransformColumnTypes(Tabla,{{"Column1", type date}}),
    CambiarNombreCampo = Table.RenameColumns(CambiATipoFecha,{{"Column1", "Fecha"}}),
    InsertaAnyo = Table.AddColumn(CambiarNombreCampo, "Año", each Date.Year([Fecha])),
    InsertaTrimestre = Table.AddColumn(InsertaAnyo, "Trimestre", each Date.QuarterOfYear([Fecha])),
    InsertaMes = Table.AddColumn(InsertaTrimestre, "Mes", each Date.Month([Fecha])),
    InsertaDia = Table.AddColumn(InsertaMes, "Día", each Date.Day([Fecha])),
    InsertDiaInt = Table.AddColumn(InsertaDia, "Año-Mes-Día", each [Año] * 10000 + [Mes] * 100 + [Día]),
    InsertaNombreMes = Table.AddColumn(InsertDiaInt, "Nombre del mes tmp", each Date.ToText([Fecha], "MMMM"), type text),
    CambiaNombreMes = Table.AddColumn(InsertaNombreMes, "Nombre del mes", each Text.Upper(Text.Range([Nombre del mes tmp], 0, 1)) & Text.Range([Nombre del mes tmp], 1, Text.Length([Nombre del mes tmp])-1)),
    EliminarMesTmp = Table.RemoveColumns(CambiaNombreMes,{"Nombre del mes tmp"}),
    InsertaMesAnyo = Table.AddColumn(EliminarMesTmp, "MesAño", each (try(Text.Range([Nombre del mes],0,3)) otherwise [Nombre del mes]) & " " & Number.ToText([Año])),
    InsertaNombreTrim = Table.AddColumn(InsertaMesAnyo, "Nombre Trimestre", each "Q" & Number.ToText([Trimestre]) & " " & Number.ToText([Año])),
    InsertaDiaSemana = Table.AddColumn(InsertaNombreTrim, "Num. Dia de la semana", each Date.DayOfWeek([Fecha])),
    InsertaNombreDiaSem = Table.AddColumn(InsertaDiaSemana, "Día de la semana tmp", each Date.ToText([Fecha], "dddd"), type text),
    CambiaNombreDiaSem = Table.AddColumn(InsertaNombreDiaSem, "Día de la semana", each Text.Upper(Text.Range([Día de la semana tmp], 0, 1)) & Text.Range([Día de la semana tmp], 1, Text.Length([Día de la semana tmp])-1)),
    EliminarColTmp = Table.RemoveColumns(CambiaNombreDiaSem,{"Día de la semana tmp"}),
    OrdenMes = Table.AddColumn(EliminarColTmp, "OrdenMes", each Number.FromText(Number.ToText([Año]) & Date.ToText([Fecha], "MM")))
  in
    OrdenMes 
in
  CreaTablaFechas

Lo ideal es guardar estas fórmulas en un archivo de texto para poder reaprovecharlas en un futuro copiando y pegando o bien utilizar la función LibPQ.

Encontraréis el código de las dos funciones también en GitHub (denominadas Fecha.CreaTablaFechaDesdeIntervalo.pq y Fecha.CreaTablaFechaDesdeHechos.pq)

Último obstaculo

Una vez hemos aplicado los cambios a Power BI es probable que queramos poner un filtro con el mes, o con el mes-año (campo MesAño), en este último caso debemos hacer una última acción debido a que la lista aparecería por orden alfabético, es decir, primero a parecería Abril, luego agosto, después diciembre, etc.

Para solucionarlo seleccionamos el campo MesAño, vamos al menú «Modelado», presionamos sobre la opción «Ordenar por columna» y seleccionamos «OrdenMes».

De este modo quedará correctamente. También podeís ordenar los meses de forma descendente mediante el menú del propio filtro (sibolizado por tres puntos …).

Más allá de las fechas

Habitualmente es necesario ir un paso más allá y requerir de algún cálculo adicional para obtener por ejemplo datos acumulados del año actual, datos de los últimos 12 meses, etc, lo que en inglés denominan time intelligence. Si nadie me corrige este tipo de fórmulas deben realizarse en DAX una vez ya tenemos los datos importados.

En una de nuestras primeras entradas publicamos un breve artículo con alguna medida de interés. En DAX Patterns tenéis de muchas formulas más y mucho mejor explicadas.

También con DAX

Al inicio de este artículo hacía referencia a que también podemos recurrir a DAX para este cometido. En Github podéis encontrar un pbix de Marco Russo con todas las fórmulas necesarias para hacerlo.