Imagina que tienes un modelo de datos con tres tablas: Ventas, Pedidos y Devoluciones. Ventas arranca en enero de 2022. Pedidos, en marzo de 2020. Devoluciones, en julio de 2021.
Creas un calendario que empieza el 1 de enero de 2022 porque es la fecha de la tabla más grande. Montas la tabla dinámica y todo parece correcto. Hasta que alguien pregunta por los pedidos de 2020 y 2021. No aparecen. Quedan fuera porque el calendario no los cubre.
El problema no es la tabla dinámica. Es que el calendario no alcanza todas las filas de todas las tablas de hechos.
Cuando trabajas con un modelo de datos en Excel, cada tabla de hechos (ventas, pedidos, devoluciones, facturas) tiene su propia columna de fecha. Todas esas columnas se relacionan con una única tabla calendario.
Esa tabla calendario debe empezar en la fecha mínima de todas las tablas y terminar en la fecha máxima de todas. Si empieza más tarde, dejas filas sin fecha. Si termina antes, cortas el análisis. Si te pasas de largo, inflas el modelo sin motivo.
Hacerlo a ojo funciona la primera vez. Pero cada cierre trimestral añades filas nuevas. El calendario se queda corto o sobra. Y no te enteras hasta que alguien pregunta por un dato que no aparece.
La solución es calcular el mínimo y el máximo de todas las tablas de hechos y generar el calendario entre esos dos extremos. Power Query lo hace en unos pocos pasos.
Primero, creas una consulta que obtiene la fecha mínima de cada tabla:
let
MinVentas = List.Min(Ventas[Fecha]),
MinPedidos = List.Min(Pedidos[Fecha]),
MinDevoluciones = List.Min(Devoluciones[Fecha]),
FechaInicio = List.Min({MinVentas, MinPedidos, MinDevoluciones})
in
FechaInicioDespués, haces lo mismo con la fecha máxima:
let
MaxVentas = List.Max(Ventas[Fecha]),
MaxPedidos = List.Max(Pedidos[Fecha]),
MaxDevoluciones = List.Max(Devoluciones[Fecha]),
FechaFin = List.Max({MaxVentas, MaxPedidos, MaxDevoluciones})
in
FechaFinCon esas dos fechas, generas el calendario:
let
FechaInicio = ...,
FechaFin = ...,
NumeroDias = Duration.Days(FechaFin - FechaInicio) + 1,
ListaFechas = List.Dates(FechaInicio, NumeroDias, #duration(1,0,0,0)),
TablaCalendario = Table.FromList(ListaFechas, Splitter.SplitByNothing(), {"Fecha"}, null, ExtraValues.Error)
in
TablaCalendarioAhora cada vez que actualices el modelo, Power Query recalcula el mínimo y el máximo. El calendario crece o se ajusta solo.
Supón que la tabla de Pedidos incluye pedidos programados para dentro de seis meses. El MAX de Pedidos será una fecha futura. El calendario se extenderá hasta esa fecha.
Eso es correcto. Si tienes pedidos programados, necesitas que el calendario los cubra. Si no los quieres en el análisis, filtra la tabla de Pedidos antes de calcular el MAX, no el calendario.
Si una de las tablas de hechos está vacía, List.Min y List.Max devuelven error. Para evitarlo, puedes comprobar si la tabla tiene filas antes de calcular el mínimo y el máximo, o usar try … otherwise para manejar el error.
Otra opción es excluir esa tabla del cálculo hasta que tenga datos. Depende de si la tabla vacía es temporal o permanente.
Una vez que tienes la tabla con la columna Fecha, añades las columnas derivadas que necesites: Año, Mes, Trimestre, Día de la semana, Número de semana, Mes-Año.
Power Query tiene funciones para todas:
Si necesitas un formato concreto para Mes-Año (por ejemplo, «2024-11»), usa Date.ToText([Fecha], «yyyy-MM»).
Añade solo las columnas que uses en las tablas dinámicas. Cada columna extra ocupa memoria.
Un modelo de datos con varias tablas de hechos necesita una única tabla calendario que las cubra todas. Hacerlo a ojo deja huecos o sobra. Hacerlo con MIN y MAX de todas las tablas te asegura que no falta ni sobra ni un día.
Mi regla: si tienes más de una tabla de hechos, calcula el calendario desde Power Query con el mínimo y el máximo de todas. Cada vez que actualices, el rango se ajusta solo.
Próximo paso: abre Power Query, crea una consulta nueva y escribe la fórmula que calcula el mínimo de tus tablas de hechos. Después haz lo mismo con el máximo. Genera el calendario entre esas dos fechas y carga la tabla al modelo.
¿Qué te ha parecido? Comparte tu experiencia o tu pregunta en los comentarios.