martes, 16 de junio de 2020

Power Query: replicando SIFECHA

Hace algunas semanas tuve la necesidad de realizar en una Consulta de Power Query un cálculo de meses enteros transcurridos entre fechas... Busqué si existía en la biblioteca de funciones M algo similar, pero para mi decepción no la encontré :-(
Sin embargo con una búsqueda profunda por Internet hallé algún ejemplo que se asemejaba a lo que necesitaba en
www.goodly.co.in/calculate-age-years-months-power-bi/
y tras una modificación del código llegué a lo que necesitaba.
Power Query: replicando SIFECHA

Así pues los pasos para crear nuestra función SIFECHA en Power Query son:
1- Ir al Editor de Consultas y acceder a Ficha Inicio > Nueva Consulta > Nueov Origen > Otros Orígenes > Consulta en blanco
2- En el editor avanzado borramos las líneas existentes
3- Añadimos el siguiente código:
(F_Ini as date, F_Fin as date, tipo as text) => let 
factorDIA= if Date.Day(F_Fin) < Date.Day(F_Ini) then -1 else 0,
Temp = Date.EndOfMonth(#date(if (Date.Month(F_Fin) + factorDIA)=0 then Date.Year(F_Fin)-1 else Date.Year(F_Fin),
if (Date.Month(F_Fin) + factorDIA)=0 then 12 else Date.Month(F_Fin) + factorDIA, 1 )), UltDIA = List.Min({Date.Day(F_Ini), Date.Day(Temp)}), factorAÑO = if Date.DayOfYear(F_Ini) <= Date.DayOfYear(F_Fin) then 0 else 1, //Cáculos de periodos básicos
dias = Number.From(F_Fin) - Number.From(#date(if (Date.Month(F_Fin) + factorDIA)=0 then
Date.Year(F_Fin)-1 else Date.Year(F_Fin),
if (Date.Month(F_Fin) + factorDIA)=0 then 12 else Date.Month(F_Fin) + factorDIA, UltDIA)), meses = Date.Month(F_Fin) - Date.Month(F_Ini) + 12*factorAÑO + factorDIA, años = Date.Year(F_Fin) - Date.Year(F_Ini)-factorAÑO, SIFECHA = if F_Fin < F_Ini then null
else
//tipologías de cálculo a semejanza de SIFECHA
if tipo="y" then años //"y" : devuelve la cantidad entera de años en el intervalo de fechas.
else if tipo="m" then años*12 + meses //"m" : devuelve la cantidad entera de meses en el intervalo de fechas.
else if tipo="md" then dias //"md" : obtendríamos la cantidad de días por encima de la cantidad entera de meses.
else if tipo="ym" then meses //"ym" : obtendríamos la cantidad de meses por encima de la cantidad entera de años.
else if tipo="d" then F_Fin-F_Ini //"d" : devuelve la cantidad entera de días entre ambas fechas.
else null

in
SIFECHA

Power Query: replicando SIFECHA

Recuerda renombrar la Consulta-Función!!; yo la he llamada 'fxDATEDIF'.
Ya podemos hacer uso de nuestra función personalizada...
Tras haber cargado nuestra tabla con fechas, aplicamos y agregamos unas cuantas columnas personalizadas, donde haremos uso de los parámetros necesarios, iguales que en SIFECHA (ver aquí)
Serían:
"y" : devuelve la cantidad entera de años en el intervalo de fechas.
"m" : devuelve la cantidad entera de meses en el intervalo de fechas.
"d" : devuelve la cantidad entera de días entre ambas fechas.
"ym" : obtendríamos la cantidad de meses por encima de la cantidad entera de años.
"md" : obtendríamos la cantidad de días por encima de la cantidad entera de meses.
El código quedaría:
let
    Origen = Excel.CurrentWorkbook(){[Name="Tabla3"]}[Content],
    #"Tipo cambiado" = Table.TransformColumnTypes(Origen,{{"INI", type date}, {"FIN", type date}, {"SIFECHA", Int64.Type}}),
    //añadimos las columnas personalizadas
    #"Personalizada agregada" = Table.AddColumn(#"Tipo cambiado", "Y", each fxDATEDIF([INI],[FIN],"y")),
    #"Personalizada agregada1" = Table.AddColumn(#"Personalizada agregada", "M", each fxDATEDIF([INI],[FIN],"m")),
    #"Personalizada agregada2" = Table.AddColumn(#"Personalizada agregada1", "D", each fxDATEDIF([INI],[FIN],"d")),
    #"Personalizada agregada3" = Table.AddColumn(#"Personalizada agregada2", "YM", each fxDATEDIF([INI],[FIN],"ym")),
    #"Personalizada agregada4" = Table.AddColumn(#"Personalizada agregada3", "MD", each fxDATEDIF([INI],[FIN],"md")),
    
    #"Tipo cambiado1" = Table.TransformColumnTypes(#"Personalizada agregada4",{{"MD", type number}, {"D", type number}, {"YM", type number}})
in
    #"Tipo cambiado1"
Como hemos visto en la primera imagen del post, y puedes comprobar con el resultado de SIFECHA, los valores devueltos son correctos!. Obviamente un uso de las funciones M de fecha (leer algo más aquí) se hace necesario...
Funciones M empleadas para componer nuestra función personalizada:
Date.Day - recupera el día de una fecha dada (como DIA en Excel)
Date.Month - recupera el mes de una fecha dada (como MES en Excel)
Date.Year - recupera el año de una fecha dada (como AÑO en Excel)
también...
Date.EndOfMonth - retorna el último día del mes de una fecha dada (como FIN.MES en Excel)
Date.DayOfYear - revuelve un número que representa el día del año, i.e., un número entre 1 y 365/366
Number.From - convierte una fecha en un valor decimal, por ejemplo, la fecha 16/06/2020 la transforma en 43.998
y finalmente
List.Min - para obtener el mínimo valor entre varios elementos...
El resto de cálculos son los necesarios para llegar a las diferencias entre las fechas tomadas, teniendo presente los intervalos a emplear: años, meses o días...

No hay comentarios:

Publicar un comentario

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