Я использую Power Query в Excel, и мне нужно рассчитать продолжительность для каждой «Door_side», используя столбец «Время» на ежедневном уровне для каждого отдельного пользователя.
Данные поступают из системы доступа на основе карты и имеют следующий формат:
Date Time User_No Door_side
03/12 08:59 User_05 Outside
03/12 09:00 User_33 Inside
03/12 09:01 User_10 Outside
03/12 09:01 User_04 Outside
03/12 09:02 User_26 Outside
03/12 09:03 User_19 Outside
03/12 09:03 User_15 Inside
03/12 09:04 User_31 Inside
03/12 09:05 User_31 Outside
03/12 09:06 User_15 Outside
03/12 09:06 User_06 Inside
03/12 09:06 User_06 Inside
03/12 09:06 User_06 Inside
03/12 09:08 User_32 Outside
03/12 09:09 User_10 Inside
03/12 09:09 User_13 Inside
03/12 09:10 User_10 Outside
Я пробовал следующее:
- Сортировка строк по дате, пользователю и времени;
- Добавлен столбец индекса;
- Создан настраиваемый столбец с именем PreviousTime;
- Расчетная продолжительность (время - предыдущее время).
Полный код для вышеупомянутых шагов:
let
Source = Table,
#"Sorted Rows" = Table.Sort(Source,{{"Date", Order.Ascending}, {"User_No", Order.Ascending}, {"Time", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "PreviousTime", each try
if List.AllTrue(
{[User_No]=#"Added Index"[User_No]{[Index]-1},[Date]=#"Added Index"[Date]{[Index]-1}
}
)
then try #"Added Index"[Time]{[Index]-1} otherwise [Time]
else [Time]
otherwise [Time]),
Duration = Table.AddColumn(#"Added Custom", "Duration", each [Time] - [PreviousTime], type duration)
in
Duration
Это работает с небольшими наборами данных, но вызывает проблемы с функциональностью и полностью не работает с большим объемом данных. Я новичок в Power Query и M, поэтому я просто не могу понять, что именно из формулы настраиваемого столбца вызывает проблемы или как подойти к этому по-другому.
Я попытался сохранить приведенный выше код как часть моего запроса, а также использовать его как функцию, но между этими двумя подходами нет большой разницы в функциональности. Обработанная таблица будет отправлена в модель данных, но я надеялся получить продолжительность в Power Query, а не в Power Pivot. Заранее большое спасибо!
Чтобы подробнее рассказать о задаче, я загрузил сокращенную версию данных для 3 пользователей за декабрь. Вы можете найти его здесь: https://1drv.ms/x/s!AocQlL_KAzymgwhqiKxSL5JMZheL.
Я хочу рассчитать продолжительность между отметками времени на основе пользователя и даты. В качестве плюса у меня нет пользователей, работающих после полуночи, поэтому все временные метки для определенной смены будут в пределах одной даты.
Пример желаемого результата также можно найти в книге и выглядит следующим образом (рассчитывается в Excel):
Date Time User Door_side Duration
03/12 06:54 User_1 Outside
03/12 07:26 User_1 Inside 00:32:00
03/12 07:27 User_1 Outside 00:01:00
03/12 07:44 User_1 Inside 00:17:00
03/12 07:52 User_1 Outside 00:08:00
03/12 08:35 User_1 Inside 00:43:00
03/12 08:36 User_1 Outside 00:01:00
03/12 11:50 User_1 Inside 03:14:00
03/12 12:01 User_1 Outside 00:11:00
03/12 13:27 User_1 Inside 01:26:00
03/12 13:43 User_1 Outside 00:16:00
03/12 14:57 User_1 Inside 01:14:00
03/12 15:20 User_1 Inside 00:23:00
03/12 15:26 User_1 Outside 00:06:00
03/12 15:34 User_1 Inside 00:08:00
Поскольку данные содержат всех пользователей и несколько дней, я пытаюсь выполнить вычисления в таблицах, сгруппированных по дате и пользователю.
Я потратил некоторое время на тестирование всех 3 подходов, представленных ниже (List.Min, Table.FirstN и вложенные таблицы), и на ограниченном наборе данных все они отлично справляются со своей задачей.
Однако при применении к большему набору данных (у меня около 20000 строк за 1 месяц) подход с вложенными таблицами кажется самым быстрым.
Спасибо Евгению и Марку за помощь и, что более важно, за то, что научили меня чему-то новому.
< / a> 
