viernes, 6 de agosto de 2021

Power Query: Última Fecha Condicionada

Me planteaba un usuario una duda respecto a la forma de obtener sobre un listado de fechas la última fecha... Por añadidura explicaré la forma de recuperar de forma condicionada la última fecha de cada mes, tal como se ve en la imagen siguiente:
Power Query: Última Fecha Condicionada

Así pues cargaremos nuestra tabla desde la ficha Datos > grupo Obtener y transformar > Desde Tabla o rango, y desde el editor de consultas de Power Query, seleccionamos nuestra tabla recien cargada y presionaremos Editor avanzado, donde implementaremos el siguiente código M:
let
    Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
    //recuperamos la columna d fechas en modo de lista
    //con el fin de aplicarle la función List.Max que nos retorna la fecha más alta...
    MaxFecha = List.Max(Origen[fechas]),
    
    //Agregamos una nueva columna, donde condicionamos el valor a añadir (la fecha calculada anterior)
    //en el caso que coincida con la fecha de la columna original 'fechas'
    AddColMaxFecha=Table.AddColumn(Origen,"Fecha máxima", each if [fechas]=MaxFecha then MaxFecha else null)
in
    AddColMaxFecha

Power Query: Última Fecha Condicionada

De especial tenemos la necesidad de obtener en forma de lista las fechas de nuestro origen, para posteriormente poder aplicar la función M List.Max

Un cálculo algo más elaborado consistiría en obtener la última fecha para cada mes!!.
Para ello generamos una consulta en blanco donde escribiremos el siguiente código:
let
    //cargamos la tabla origen y modificamos el tipo de datos de las dos columnas...
    Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
    TipoCambiado = Table.TransformColumnTypes(Origen,{{"fechas", type date}, {"eur", Int64.Type}}),
    
    //añadimos una nueva columna con el nombre del mes
    //es importante ya que nos basaremos en este campo para aplicar el criterio posterior de agrupación
    Mes=Table.AddColumn(TipoCambiado,"Mes", each Date.MonthName([fechas])),
    //OJO!!!, si hubiera diferentes años en las fechas de la tabla original tendríamos que añadir el año
    
    //Generamos una agrupación por cada Mes (en letras) obtenido en el paso anterior
    //donde aplicamos el cálculo de Máximo para el campo de 'fechas'
    //además, aprovechamos para definir el tipo de datos devuelto!!!
    AgrupoPorMes=Table.Group(Mes, 
                            {"Mes"}, 
                            {{"MaxFecha", each List.Max([fechas]), type nullable date}, {"DetalleFechas", each _, type table [fechas=nullable date, eur=nullable number, Mes=text]}}),
    //Expandimos y extraemos las columnas...
    Expande_DetalleFechas = Table.ExpandTableColumn(AgrupoPorMes, "DetalleFechas", {"fechas", "eur"}, {"fechas", "eur"}),
    
    //Agregamos una nueva columna que compare la fecha máxima para cada mes obtenida en el paso previo
    //con la fecha original
    Condicional = Table.AddColumn(Expande_DetalleFechas, "Ult_Fecha", each if [MaxFecha] = [fechas] then [MaxFecha] else null),
    
    //quitamos columnas intermedias...
    ColumnasQuitadas = Table.RemoveColumns(Condicional,{"Mes", "MaxFecha"})
in
    ColumnasQuitadas

Power Query: Última Fecha Condicionada


Me parece interesante resaltar la parte de la agrupación por el mes, lo que nos proporciona una 'subtabla' sobre la que poder aplicar la función List.Max sobre los registros con fecha del mes...
Power Query: Última Fecha Condicionada

Fechas que el paso siguiente empleamos para comparar...
Power Query: Última Fecha Condicionada


Un interesante y sobre todo práctico ejercicio con Power Query.

No hay comentarios:

Publicar un comentario

Nota: solo los miembros de este blog pueden publicar comentarios.