Encontrar fechas mínimas y máximas en Power Query

Cuando se habla de análisis de datos en Power BI es obligatorio tener una tabla de fechas. Tenemos diferentes métodos para crear una tabla de fechas, ya sea en DAX o en Power Query. En DAX usamos la función CALENDAR() o CALENDARAUTO(); en Power Query podemos usar una combinación de las funciones List.Dates(), #date() y #duration().

Del modo que sea, siempre nos encontramos con el dolor de encontrar el rango de fechas adecuado, empezando por una fecha pasada y acabando en una fecha futura, pues esto cubre todas las fechas relevantes dentro de nuestro modelo de datos. Y claro, podemos contar con que nuestro cliente sepa, pero por desgracia no siempre es el caso.

Esto particularmente sucede con la fecha de inicio, ya que por lo regular la respuesta del cliente es algo entre las líneas de «mirar los datos para saber», lo cual es una respuesta válida, ya que bien podemos buscar todas las columnas que tengan tipos de datos Date o DateTime, acomodarlos en orden ascendente o descendente y resolverlo, pero esto no es tan simple cuando se tratan de muchas fechas, pues el proceso se vuelve inmensamente largo.

Aquí podríamos pensar que si usamos CALENDARAUTO() en DAX ya lo tenemos cubierto, pero esto no es del todo correcto. En muchos casos hay columnas de tipo Date o DateTime que no deben ser consideradas dentro de nuestro espectro de fechas, como fechas de nacimiento, por ejemplo (lo cual veremos más adelante).

Query para encontrar fechas en Power Query

El código que vamos a ver fue creado por Soheil Bakhshi y nos va a ayudar a identificar fechas de una forma más automatizada. Funciona de la siguiente manera, obteniendo todos los queries existentes que usen:

• Variable intrínseca #sections
• Filtran el nombre de query actual, el cual será GetMinMaxAllDates en este ejemplo, lo cual nos ayudará a evitar el siguiente error:

• Filtran todos los queries que NO son de tipo table
• Añaden una nueva columna estructurada llamada TableSchema que incluye todas las estructuras de tablas
• Expanden la columna estructurada TableSchema manteniendo las columnas Name y Kind y renombrando la columna Name a Column Name y la columna Kind a Datatype
• Filtra los resultados para mantener solo las columnas con tipos de datos Date o DateTime
• Filtran los valores innecesarios del nombre de la columna, tales como fecha de nacimiento
• Añade una nueva columna llamada Min Date que contiene el valor mínimo de la columna que aparece en la columna Column Name del valor de la tabla que aparece en la columna Value
• Añade otra columna llamada Max Date similar a como se creó Min Date
• Extrae el valor mínimo de la columna Min Date
• Extrae los valores máximos de la columna Max Date
• Muestra los últimos dos como una lista

Todo esto puede sonar confuso ahora, así que será mejor verlo en acción. Para una solución rápida aquí están las expresiones de Power Query que se usan:

let
   AllQueries = #sections,
   RecordToTable = Record.ToTable(AllQueries[Section1]),
   FilterOutCurrencyQuery = Table.SelectRows(
      RecordToTable, 
      each 
         [Name]<>"GetMinMaxAllDates" 
      and 
         Type.Is(Value.Type([Value]),
      type table)
   =true),
   AddTableSchemaColumn = Table.AddColumn(
      FilterOutCurrencyQuery,
      "TableSchema",
      each
         try Table.Schema([Value])
         otherwise null),
   ExpandTableSchema = Table.Buffer(
      Table.ExpandTableColumn(
      AddTableSchemaColumn,
      "TableSchema",
      {"Name", "Kind"},
      {"Column Name", "Datatype"})),
   FilterTypes = Table.SelectRows(
      ExpandTableSchema,
      each
         ([Datatype] = "datetime"
      or
         [Datatype] = "date")),
   AddedMinDateColumn = Table.AddColumn(
      FilterTypes,
      "Min Date",
      each
         Date.From(
            List.Min(
               Table.Column([Value], [Column Name])))),
   AddedMaxDateColumn = Table.AddColumn(
      AddedMinDateColumn,
      "Max Date",
      each
         Date.From(
            List.Max(
               Table.Column([Value], [Column Name])))),
   FilterOutUnnecessaryColumns = Table.SelectRows(
      AddedMaxDateColumn,
      each
         ([Column Name] <> "BirthDate")),
   MinDate = List.Min(
      List.Combine(
         {FilterOutUnnecessaryColumns[Min Date],
         FilterOutUnnecessaryColumns[Max Date]})),
   MaxDate = List.Max(
      List.Combine(
         {FilterOutUnnecessaryColumns[Min Date],
         FilterOutUnnecessaryColumns[Max Date]})),
   MinMaxDates = {"Min Date = " & Text.From(MinDate), "Max Date = " & Text.From(MaxDate)}
in
   MinMaxDates

La siguiente imágen nos muestra el resultado de correr el código anterior en el editor de Power Query, teniendo 4 tablas llamadas desde Analysis Service. Esas tablas contienen columnas con datos ya sean del tipo Date o cualquier otro.

Explicación detallada del Query

Una vez más necesitaremos pasar el nombre del query actual en la expresión de arriba, en este caso el nombre del query actual es GetMinMaxAllDates tal como se muestra en la siguiente imagen:

Antes se mencionó que en muchos casos no queremos que todas las columnas Date o DateTime sean cubiertas por la tabla Date. Si no notamos eso entonces podemos crear un montón de fechas irrelevantes en nuestra tabla de Fechas, tal y como podemos ver en Min Date en la imágen de arriba. En esa misma imagen podemos ver también un paso adicional FilterOutUnnecesaryColumns, al cual le daremos click para filtrar todos los valores innecesarios de la columna Column Name tal y como se muestra a continuación:

Daremos click a este último paso que es MinMaxDates para ver los valores como se muestran a continuación:

Al correr el query de arriba obtenemos el rango de fechas valido, por lo que podemos crear una tabla de Fechas con cualquier método de nuestra elección, ya sea mediante Power Query o DAX usando el rango de fecha mostrado anteriormente. Hay que recordar que crear la tabla de Fechas es un proceso completamente distinto y que este query solo nos ayudará a encontrar fechas mínimas y máximas válidas dentro de todas las tablas que carguemos en el editor de Power Query.

Y ya para cerrar tenemos un par de cosas a considerar

• Las tablas mostradas en este ejemplo tienen en total 4 millones de filas. El query GetMinMaxAllDates corrió en aproximadamente dos segundos, lo cual no está nada mal. Aun así, en tablas más grandes puede que le tome más tiempo darnos los resultados.
• Debemos tener algunos queries ya cargados dentro del editor de Power Query
• Este método también funciona en el modo Query Directo, pero esto conlleva a que le tome más tiempo darnos los resultados
• El query de arriba recupera la fecha mínima y máxima a través de todas las tablas. Cuando creamos una tabla de Fechas, hay que tener en cuenta que la columna de Fecha debe empezar desde el primero de Enero de la fecha mínima, yendo así hasta el 31 de Diciembre de la fecha máxima.
• Este método NO cuenta con soporte en flujos de datos de Power BI.

Cualquier duda nos leemos abajo.