power query自定义查询函数（中午休息一小时

原创于 2025-08-24 19:41:05 发布 · 900 阅读

8 ·

CC 4.0 BY-SA版权

文章标签：

#power query

KPI 类POWERBI

需求：

Unstuff date & time -货到了 [UNSTUFFING_DATETIME1]
#targeted received date & time. [TargetedReceivedDateTime]
大部分supplier + 8 working hour. [WorkingHours]
两个supplier + 4 workinghour.：Armor Asia Imaging Supplies Pte Ltd和Transam Industries Pte Ltd
Working hours:
Mondy - Friday 9AM - 5PM. 12PM - 1PM break
Sat 9AM - 12PM
Sun off
Public holiday off Holidays表

函数：fnCalculateTargetDateTime:

(UnstuffingDateTime as datetime, WorkingHours as number, Holidays as table) as datetime =>
let
// 判断是否为工作日
IsWorkingDay = (date as date) =>
let
DayOfWeek = Date.DayOfWeek(date, Day.Monday),
IsHoliday = Table.Contains(Holidays, [HolidayDate = date])
in
not IsHoliday and (DayOfWeek <= 5), // 周一至周五且非假期

CalculateTarget =
let
func = (remainingMinutes as number, current as datetime) =>
let
CurrentDate = DateTime.Date(current),
CurrentTime = DateTime.Time(current),
CurrentYear = Date.Year(CurrentDate),
CurrentMonth = Date.Month(CurrentDate),
CurrentDay = Date.Day(CurrentDate),
IsSaturday = Date.DayOfWeek(CurrentDate, Day.Monday) = 5,

MorningSlot = [
Start = #datetime(CurrentYear, CurrentMonth, CurrentDay, 9, 0, 0),
End = #datetime(CurrentYear, CurrentMonth, CurrentDay, 12, 0, 0)
],
AfternoonSlot = [
Start = #datetime(CurrentYear, CurrentMonth, CurrentDay, 13, 0, 0),
End = #datetime(CurrentYear, CurrentMonth, CurrentDay, 17, 0, 0)
],

WorkSlots = if not IsWorkingDay(CurrentDate) then {}
else if IsSaturday then {MorningSlot}
else {MorningSlot, AfternoonSlot},

ProcessSlots = List.Accumulate(
WorkSlots,
[Remaining = remainingMinutes, Current = current, Done = false],
(state, slot) =>
if state[Done] or state[Remaining] <= 0 then state
else
let
SlotStart = if state[Current] > slot[Start] then state[Current] else slot[Start],
AvailableMinutes = if SlotStart >= slot[End] then 0
else Duration.TotalMinutes(slot[End] - SlotStart),
MinutesToUse = if AvailableMinutes > state[Remaining]
then state[Remaining]
else AvailableMinutes,

NewCurrent = SlotStart + #duration(0, 0, MinutesToUse, 0),
NewRemaining = state[Remaining] - MinutesToUse,
IsDone = NewRemaining <= 0
in
[Remaining = NewRemaining, Current = NewCurrent, Done = IsDone]
),


NextDate =
if ProcessSlots[Done] then ProcessSlots[Current]
else @func(
ProcessSlots[Remaining],
#datetime(
Date.Year(Date.AddDays(CurrentDate, 1)),
Date.Month(Date.AddDays(CurrentDate, 1)),
Date.Day(Date.AddDays(CurrentDate, 1)),
9, 0, 0)
)
in
NextDate,

InitialRemaining = WorkingHours * 60
in
func(InitialRemaining, UnstuffingDateTime)
in
CalculateTarget