Inserir consulta Nula no Power Query | Editor Avançado
Opção 1:
//Fonte: https://github.com/pietrofarias/PatternsM
fn = (startDate como data, EndDate como data, opcional InclusiveStartDate como lógico anulável, opcional HolidayDatesList como lista) /*como número*/ => deixar HolidayDates = se HolidayDatesList for nulo, então {} senão HolidayDatesList, InclusiveDay = se InclusiveStartDate for nulo então false senão InclusiveStartDate, DurationDays = Duration.Days(EndDate - startDate), GerarLista = List.Buffer( List.Generate( () => [ Contador = 0, StartDate = Date.From(startDate), Holiday = List.MatchesAny(HolidayDates, cada Date.From(_) = StartDate), Run = Counter <= Number.Abs(DurationDays) ], cada [Run] = true e [StartDate] <= EndDate, cada [ StartDate = Date.AddDays([StartDate], 1), Holiday = List.MatchesAny(HolidayDates, cada Date.From(_) = StartDate), Contador = if Date.DayOfWeek(StartDate, Day.Saturday) > 1 e não feriado então [Contador] + 1 senão [Contador], Run = Counter <= Number.Abs(DurationDays) ], cada [DataInício] ) ), RemoveDateWeekndayAndHoliday = Lista.Selecione( GerarLista, cada não ( deixar DataRef = _, IsWeekDay = Date.DayOfWeek(DateRef , Day.Saturday) > 1, ÉFeriado = se HolidayDates for null então falso else List.MatchesAny(HolidayDates, cada Date.From(_) = DateRef) em IsWeekDay = false ou IsHoliday = true ) e ( if InclusiveDay então _ >= startDate else _ > startDate ) ), Result = List.Count(RemoveDateWeekndayAndHoliday) em Resultado, fnType = tipo de função ( startDate como data, EndDate como data, opcional InclusiveStartDate como (tipo meta lógico [ Documentation.AllowedValues={true,false}]), opcional HolidayDatesList como lista ) como meta de data [ Documentação.Name = "fnNumberWorkDay", Documentation.LongDescription = "Descrições em pt-BR: não estavam função retorna a quantidade de dias úteis com base em um dado de referência (startDate), considerando uma lista de feriados (opcional) e possibilidade de se incluir com a data de referência (startDate ) na contagem. Por padrão não é inclusivo.", Documentação.Exemplos = { [ Description = "Esta função retorna a quantidade de dias úteis, desconsiderando uma os feriados da lista HolidayDatesQuery (lista de dados) e os finais de semana", Code = "NumberWorkDay(#date(2020,12,31), #date(2020,1,7), false, HolidayDatesQuery)", Resultado = "4" ], [ Description = "Esta função retorna a quantidade de dias úteis, desconsiderando apenas o final de semana", Code = "LastOrNextWorkDay(#date(2020,12,31), #date(2020,1,6), false)", Resultado = "4" ], [ Description = "Esta função retorna a quantidade de dias úteis, desconsiderando apenas o final de semana e incluindo a data de referência na contagem", Code = "LastOrNextWorkDay(#date(2020,12,31), #date(2020,1,5), false)", Resultado = "4" ], [ Description = "Esta função retorna a quantidade de dias úteis, desconsiderando apenas o final de semana, incluindo a data de referência na contagem, mas a data de referência é um sábado", Code = "LastOrNextWorkDay(#date(2021,1,2), #date(2020,1,7), true)", Resultado = "4" ] } ] em Value.ReplaceType(fn, fnType)
Opção 2:
fonte: https://gorilla.bi/power-query/working-days-between-dates/
let
// Define start and ending dates
DateStart = #date( 2022, 10, 1 ),
DateEnd = #date( 2022, 12, 31 ),
// Find the number of days between Start and End Date
DaysBetween = Duration.Days( DateEnd - DateStart ),
// The number of days should create a series that begins at the start
// date and ends at the ending date. You need an additional day here.
NumberOfDays = DaysBetween + ( if DateEnd < DateStart then - 1 else + 1 ),
// Direction indicates date series direction (positive or negative)
Direction = Number.Sign( NumberOfDays ),
// Creates dates between Start and End Date. Series can increment
// or decrement with each step, decided by the Direction variable.
ListOfDates =
List.Dates(
DateStart,
Number.Abs( NumberOfDays ),
#duration( Direction, 0, 0, 0 ) // positive or negative steps
),
ListOfWeekDays =
List.Select(
ListOfDates, // exclude weekends
each Date.DayOfWeek( _, Day.Monday ) + 1 <= 5
),
// List of holidays to exclude
Holidays = { #date(2022, 12, 26) },
// Selects only the non-holiday dates.
ListOfWorkDays =
List.Difference(
ListOfWeekDays,
Holidays ?? {} // Remove Holidays. When blank, remove nothing
),
// Counts the number of working days
NumberOfWorkDays = List.Count( ListOfWorkDays ),
// Multiplies the number of working days by 1 or -1, depending on
// whether starting date is before or after ending date.
Positive_or_negative_WorkingDays = NumberOfWorkDays * Direction
in
Positive_or_negative_WorkingDays
Opção 3:
A próxima diferença é que, quando um usuário não preenche a data inicial ou a data final, a função usará 1º de janeiro de 1900 como padrão. Usamos o operador COALESCE em M para conseguir isso.
Da mesma forma, quando uma lista de feriados é deixada em branco, a função abaixo usa uma lista vazia. Também aqui o operador COALESCE vem ao resultado.
O código de dias úteis finais para a função é:
let func =
(StartDate, EndDate, optional Holidays as list ) =>
let
// When a date is null value, start calculating from 1 Jan 1900
DateStart = StartDate ?? #date( 1900, 1, 1 ),
DateEnd = EndDate ?? #date( 1900, 1, 1 ),
// Find the number of days between Start and End Date
DaysBetween = Duration.Days( DateEnd - DateStart ) ,
// The correct date series requires the number of days FROM start to end,
// not the number of days BETWEEN start and end.
NumberOfDays = DaysBetween + (if DateEnd < DateStart then -1 else + 1 ),
// Direction indicates date series direction (positive or negative)
Direction = Number.Sign( NumberOfDays ),
Result =
List.Count(
List.Difference(
List.Select(
List.Dates(
DateStart,
Number.Abs( NumberOfDays ),
#duration( Direction, 0, 0, 0) // positive or negative steps
),
each Date.DayOfWeek( _, Day.Monday ) + 1 <= 5 // excl weekends
),
Holidays ?? {} // exclude holidays. If omitted, use empty list
)
)
* Direction // Show number of days positive or negatively
in Result,
documentation = [
Documentation.Name = " fxNetWorkDays ",
Documentation.Description = " Adds a running total column to a table, based on a value column and one or more group by columns.",
Documentation.LongDescription = " This function computes the number of working days between two dates. It takes into accounts weekends and optionally a list of holidays. Leaving out the starting date or ending date results in the function using 1 January 1900 for calculating the number of working days. ",
Documentation.Category = " Net Work Days ",
Documentation.Source = " BI Gorilla – https://gorilla.bi ",
Documentation.Version = " 1.0 ",
Documentation.Author = " Rick de Groot ",
Documentation.Examples = {[Description = " ",
Code = " let
StartDate = #date( 2022, 10, 1 ),
EndDate = #date( 2022, 12, 31 ),
ValuHolidays = #date( 2022, 12, 26 ),
Result = fxRunningTotalByCategory( StartDate, EndDate, Holidays )
in
Result ",
Result = " 60
"]}]
in
Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation) )