Как в Power Query рассчитать общее время, отработанное за несколько дней, на основе графика рабочих смен и без учета выходных?
Привет, мир переполнения стека. Спасибо за внимание. У меня есть 2 таблицы со следующими данными в Power Query, показанными ниже.
Таблица 1: График смен
Таблица 2: Рабочие процессы
Мне нужна формула Power Query, которая рассчитывает продолжительность процесса в таблице 2, которая учитывает часы смены из таблицы 1 и сверхурочную работу. В выходные и праздничные дни смены и сверхурочные работы отсутствуют. Например, в Таблице 2:
Процесс 1: Продолжительность = часы, затраченные на процесс в рабочее время с понедельника 3 июня по пятницу 7 июня. = часы понедельника (с 10:30:00 до 22:10:00) 11.67 + часы вторника (полный день) 14.67 + часы среды (полный день) 14.67 + часы четверга (полный день) 14.67 + часы пятницы (07:00:00) до 17:40:00) 11.42 = 67,10 часов
Процесс 2: Продолжительность = часы, потраченные на процесс в рабочее время со вторника 4 июня по вторник 11 июня. = часы вторника (с 11:00:00 до 22:10:00) 11.17 + часы среды (полный день) 14.67 + часы четверга (полный день) 14.67 + часы пятницы (полный день) 11.92 + часы понедельника (полный день) 14.67 + Вт (с 07:30:00 до 10:00:00) 2.50. = 69,60 часов
Процесс 3: Продолжительность = часы, потраченные на процесс в рабочее время со среды 5 июня по июнь. = (с 11:55:00 до 22:10:00) = 10,25 часов
Процесс 4: Продолжительность = часы, потраченные на процесс в рабочее время с четверга 6 июня по июнь. = (с 14:00:00 до 14:00:01) = 0,00028 часов
Процесс 5: Продолжительность = часы, потраченные на процесс в сверхурочное время (вне смены) с понедельника 1 ноября по вторник 02 ноября. = (с 23:38:59 до 00:35:51) = 0,95 часа
(Все значения выше округлены для простоты)
Я пытался жестко запрограммировать его и использовал различные операторы if/else, но ничто не дает нужных мне точных и повторяемых результатов, независимо от того, какие входные данные содержатся в таблице 2. Предполагается, что формат данных всегда будет правильным, а значения NULL не допускаются.
Любая помощь будет безмерно оценена! Благодарю вас <3
Нет, это не входит в мои намерения, 5-минутное совпадение не должно учитываться дважды. Спасибо, что указали на это!
Ответы 1
Код Powerquery для таблицы рабочих процессов
let Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Process", Int64.Type}, {"Start DateTime", type datetime}, {"Start Day", type text}, {"End DateTime", type datetime}, {"End Day", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "date", each {Number.IntegerDivide(Number.From([Start DateTime]),1)..Number.IntegerDivide(Number.From([End DateTime]),1)}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "date"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"date", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "day", each Text.Start(Date.DayOfWeekName([date], "en-US"),3),type text),
#"Merged Queries" = Table.NestedJoin(#"Added Custom1", {"day"}, #"Shift Patterns", {"Day"}, "Table2", JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Early Start Time", "Late End Time", "Total Work Hours"}, {"Early Start Time", "Late End Time", "Total Work Hours"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Table2", each ([Early Start Time] <> null)),
#"Added Custom2" = Table.AddColumn(#"Filtered Rows", "duration", each
if Time.From([Start DateTime]) > Time.From ([Late End Time]) and Number.IntegerDivide(Number.From([Start DateTime]),1) = Number.IntegerDivide(Number.From([date]),1) // start after end time -- you are working overtime
then Duration.TotalHours(#time(23,59,59)-Time.From([Start DateTime])) else
if Time.From([End DateTime]) < Time.From ([Early Start Time]) // start before start time -- you are working overtime
then Duration.TotalHours(Time.From([End DateTime])-#time(0,0,0)) else
if Number.IntegerDivide(Number.From([Start DateTime]),1) = Number.IntegerDivide(Number.From([End DateTime]),1) // end date = start date
then Duration.TotalHours(Time.From(Time.From([End DateTime]))-Time.From([Start DateTime])) else
if Number.IntegerDivide(Number.From([End DateTime]),1) = Number.IntegerDivide(Number.From([date]),1) // last date of multiple dates
then Duration.TotalHours(Time.From(Time.From([End DateTime]))-Time.From([Early Start Time])) else
if Number.IntegerDivide(Number.From([Start DateTime]),1) = Number.IntegerDivide(Number.From([date]),1) // first date of multiple dates
then Duration.TotalHours(Time.From(Time.From([Late End Time]))-Time.From([Start DateTime])) else
[Total Work Hours]),
#"Grouped Rows" = Table.Group(#"Added Custom2", {"Process", "Start DateTime", "Start Day", "End DateTime", "End Day"}, {{"duration", each List.Sum([duration]), type number}})
in #"Grouped Rows"
Моя математика времени неверна, вы правы! Извините за путаницу. Спасибо вам за публикацию. Я попробовал ваш запрос, но, похоже, он возвращает неправильные значения, если фактическая продолжительность процесса очень мала, например в секундах. Например, если Start DateTime = 21 октября 2021 г. 10:37:37, End DateTime = 21 октября 2021 г. 10:37:38, результат Duration возвращает 11,53975194. Правильная длительность — 0,000277 (1 секунда в часах).
Большое спасибо! Я отредактировал свой первоначальный вопрос, чтобы исправить ошибки, на которые вы любезно указали. Кажется, ваш измененный код работает. Однако если есть экземпляры, когда DateTime начала/окончания процесса выходит за пределы диапазонов в таблице «Схемы смен», выходные данные «Продолжительность» возвращают отрицательные значения. Это ситуации, когда инженеры приступили к работе раньше или оставались дольше, чем ожидалось (сверхурочно), для выполнения задач. Можно ли каким-то образом изменить код, чтобы учесть это?
Например: если Дата и время начала = (Пн) 11.01.2021 23:38:59, Дата и время окончания = (Вт) 11.02.2021 00:35:51; Длительность понедельника = -1,483120278, Длительность вторника = -6,902494444. Ожидаемая общая продолжительность = 0,947777778.
Исходный вопрос теперь изменен, чтобы включить этот сценарий. Спасибо!
Тывм<3 подойдёт!
Анализируя ваши ожидаемые результаты, выясняется, что вы дважды считаете пять (5) минут времени, отработанного между
Late Start TimeиEarly End Time, если что-то работало в течение этого времени. Это ваше намерение?