Código Power Query M: encuentre el número de días hábiles entre 2 fechas

Hola,

¿Puede decirme cómo encontrar la cantidad de días hábiles entre 2 fechas usando el código Power Query / M (tengo una tabla con todos los días festivos enumerados para mi país). Mi fórmula de código M actual es:

Número de días: [EndDate]-[StartDate]

Gracias,

CM

@fhill También es una buena solución, pero creo que List.Generate fue un error.

Además, su solución será mucho más rápida si la tabla de vacaciones está almacenada en búfer.

Esto es lo que hice de su solución:

let
    BufferedHolidays = Table.Buffer(Holidays),
    Source = Data,
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "DatesBetween", each { Number.From([StartDate])..Number.From([EndDate]) }),
    #"Expanded DatesBetween" = Table.ExpandListColumn(#"Added Custom", "DatesBetween"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded DatesBetween",{{"DatesBetween", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "IsHoliday", 
        each if List.Contains ( Table.Column(BufferedHolidays, "Date") ,  [DatesBetween] ) 
        or Date.DayOfWeek ( [DatesBetween] ) = 0
        or Date.DayOfWeek ( [DatesBetween] ) = 6
        then 0 else 1),
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"StartDate", "EndDate", "Index", "Excel networkdays"}, {{"Networkdays FHill", each List.Sum([IsHoliday]), type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Index"})
in
    #"Removed Columns"

Una solución aún más rápida es traducir la lógica de la función de mi publicación anterior en una consulta, por lo que las fusiones no se realizarán con tablas anidadas individuales, sino con la tabla completa a la vez.

Un inconveniente es que el código de consulta se vuelve algo más complejo.

let
    Weekend = #table(type table[Weekday = Int64.Type],{{0},{6}}),
    Source = Data,
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Dates", each Table.FromColumns({List.Dates([StartDate],1+Duration.Days([EndDate]-[StartDate]),#duration(1,0,0,0))}, type table[Dates = date])),
    #"Expanded Dates" = Table.ExpandTableColumn(#"Added Custom", "Dates", {"Dates"}),
    #"Inserted Day of Week" = Table.AddColumn(#"Expanded Dates", "Day of Week", each Date.DayOfWeek([Dates]), type number),
    #"Merged Queries" = Table.NestedJoin(#"Inserted Day of Week",{"Day of Week"},Weekend,{"Weekday"},"Inserted Day of Week",JoinKind.LeftAnti),
    #"Merged Queries1" = Table.NestedJoin(#"Merged Queries",{"Dates"},Holidays,{"Date"},"Holidays",JoinKind.LeftAnti),
    #"Grouped Rows" = Table.Group(#"Merged Queries1", {"Index"}, {{"PQ Networkdays", each Table.RowCount(_), type number}}),
    #"Merged Queries2" = Table.NestedJoin(#"Added Index",{"Index"},#"Grouped Rows",{"Index"},"Grouped Rows",JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries2", "Grouped Rows", {"PQ Networkdays"}, {"PQ Networkdays"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Grouped Rows",null,0,Replacer.ReplaceValue,{"PQ Networkdays"}),
    #"Sorted Rows" = Table.Sort(#"Replaced Value",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"

Hola @CloudMonkey,

¿Podría marcar la respuesta correcta como solución o compartir la solución si es conveniente para usted? Eso será de gran ayuda para los demás.

¡Atentamente!
Valle

Creé su tabla de días festivos con una lista de días festivos con una columna de fecha de cada día festivo.

1. Si aún no lo ha hecho, agregue una columna de índice a sus datos de fecha de inicio/fecha de finalización. Esto se usará en el último paso para reagrupar nuestros datos.

2: Aquí está el código M para crear una LISTA de fechas entre cada fecha. Esto es necesario para comparar cada fecha con los días festivos/días de la semana. Después de crear las LISTAS, expanda la columna. ** Ignorar que falta la columna Índice **

{ Número.Desde([StartDate])..Número.Desde([EndDate]) }

3: Así es como se ven los datos con el Índice ampliado y Fechas entre

4: Aquí está el código para crear una columna personalizada ‘IsHolidy’ que produce un 0 si DatesBetween coincide con una fecha en la tabla de vacaciones, o un domingo o un sábado. 1 se llena por cada MF no festivo.

=
Si
List.Contains ( Table.Column(Vacaciones, «Fecha») , List.Select ( [DatesBetween]) )
o Fecha.DíaDeLaSemana ( [DatesBetween] ) = 0
o Fecha.DíaDeLaSemana ( [DatesBetween] ) = 6
entonces 0
más 1
)

5: Ahora puede agrupar los datos por Índice y SUM IsHoliday para determinar la cantidad de días hábiles entre cada fecha.

@fhill También es una buena solución, pero creo que List.Generate fue un error.

Además, su solución será mucho más rápida si la tabla de vacaciones está almacenada en búfer.

Esto es lo que hice de su solución:

let
    BufferedHolidays = Table.Buffer(Holidays),
    Source = Data,
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "DatesBetween", each { Number.From([StartDate])..Number.From([EndDate]) }),
    #"Expanded DatesBetween" = Table.ExpandListColumn(#"Added Custom", "DatesBetween"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded DatesBetween",{{"DatesBetween", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "IsHoliday", 
        each if List.Contains ( Table.Column(BufferedHolidays, "Date") ,  [DatesBetween] ) 
        or Date.DayOfWeek ( [DatesBetween] ) = 0
        or Date.DayOfWeek ( [DatesBetween] ) = 6
        then 0 else 1),
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"StartDate", "EndDate", "Index", "Excel networkdays"}, {{"Networkdays FHill", each List.Sum([IsHoliday]), type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Index"})
in
    #"Removed Columns"

Una solución aún más rápida es traducir la lógica de la función de mi publicación anterior en una consulta, por lo que las fusiones no se realizarán con tablas anidadas individuales, sino con la tabla completa a la vez.

Un inconveniente es que el código de consulta se vuelve algo más complejo.

let
    Weekend = #table(type table[Weekday = Int64.Type],{{0},{6}}),
    Source = Data,
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Dates", each Table.FromColumns({List.Dates([StartDate],1+Duration.Days([EndDate]-[StartDate]),#duration(1,0,0,0))}, type table[Dates = date])),
    #"Expanded Dates" = Table.ExpandTableColumn(#"Added Custom", "Dates", {"Dates"}),
    #"Inserted Day of Week" = Table.AddColumn(#"Expanded Dates", "Day of Week", each Date.DayOfWeek([Dates]), type number),
    #"Merged Queries" = Table.NestedJoin(#"Inserted Day of Week",{"Day of Week"},Weekend,{"Weekday"},"Inserted Day of Week",JoinKind.LeftAnti),
    #"Merged Queries1" = Table.NestedJoin(#"Merged Queries",{"Dates"},Holidays,{"Date"},"Holidays",JoinKind.LeftAnti),
    #"Grouped Rows" = Table.Group(#"Merged Queries1", {"Index"}, {{"PQ Networkdays", each Table.RowCount(_), type number}}),
    #"Merged Queries2" = Table.NestedJoin(#"Added Index",{"Index"},#"Grouped Rows",{"Index"},"Grouped Rows",JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries2", "Grouped Rows", {"PQ Networkdays"}, {"PQ Networkdays"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Grouped Rows",null,0,Replacer.ReplaceValue,{"PQ Networkdays"}),
    #"Sorted Rows" = Table.Sort(#"Replaced Value",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"

Puede nombrar esta función NetWorkDays y agregar una columna a su tabla, invocando esta función personalizada.

Los resultados son iguales a la salida de la función de Excel NETWORKDAYS.

(StartDate as date, EndDate as date, HolidayList as list) as number =>
let
    Weekend = #table(type table[Weekday = Int64.Type],{{0},{6}}),
    Holidays = Table.FromColumns({HolidayList},type table[Weekday = date]),
    Dates = Table.FromColumns({List.Dates(StartDate,1+Duration.Days(EndDate-StartDate),#duration(1,0,0,0))}, type table[Dates = date]),
    #"Inserted Day of Week" = Table.AddColumn(Dates, "Day of Week", each Date.DayOfWeek([Dates]), type number),
    #"Merged Queries" = Table.NestedJoin(#"Inserted Day of Week",{"Day of Week"},Weekend,{"Weekday"},"Inserted Day of Week",JoinKind.LeftAnti),
    #"Merged Queries1" = Table.NestedJoin(#"Merged Queries",{"Dates"},Holidays,{"Weekday"},"Holidays",JoinKind.LeftAnti),
    NetWorkdays = Table.RowCount(#"Merged Queries1")
in
    NetWorkdays

Anónimo

Hola marcel,
Gracias por esta solución. Creé esta función e importé la lista de vacaciones como una tabla. Sin embargo, cuando invoco esta función en mi conjunto de datos para agregar una nueva columna, no me permite elegir la lista de vacaciones de la tabla de vacaciones. El menú desplegable para seleccionar la tabla de vacaciones está deshabilitado. ¿Puedes ayudarme?

Gracias por proporcionar esta solución, funciona bien para mí. Mis requisitos deben devolver un número negativo si StartDate>EndDate, por lo que utilicé una declaración if para probar eso y una llamada de la función para cada condición.

Entradas relacionadas

Deja un comentario Cancelar respuesta