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. 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:
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:
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...
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. 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
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.