Как группировать по имени и сумме времени в Power Query
Контекст: я извлек необработанные данные из системы входа/выхода часов, которая показывает мне, когда люди входили или выходили из офиса. Мне нужно преобразовать данные, чтобы я мог рассчитать некоторые статистические данные и диаграммы.
Ввод: последний шаг моего Power Query выглядит так:
Желаемый результат: я хочу сгруппировать строки по имени и дате и суммировать столбцы времени, чтобы время входа/выхода было в одной строке для каждого имени и даты. Это должно выглядеть так:
Проблема: когда я пытаюсь суммировать даты (агрегировать), я получаю «ошибку» в большинстве ячеек.
= Table.Group(#"Filtered Pablo", {"Name", "Date"},
{{"AM_In", each List.Sum([AM_In_raw]), type any},
{"AM_Out", each List.Sum([AM_Out_raw]), type any},
{"PM_In", each List.Sum([PM_In_raw]), type any},
{"PM_Out", each List.Sum([PM_Out_raw]), type any}})
Вопрос: Каков хороший способ объединить несколько строк в одну, содержащую все допустимые значения, с помощью Power Query в Excel?
Заранее благодарим за любую подсказку, ведущую к рабочему решению. Если есть передовой опыт или метод решения проблем такого типа в различных сценариях, мы будем очень признательны.
Решения SQL или Python также приветствуются.
Ответы 1
Независимо от того, агрегируете ли вы (суммируете) значения времени или другие типы значений в Power Query в Excel, важно убедиться, что значения находятся в формате, который он может обрабатывать. Время входа/выхода часов часто выражается в виде времени, например часов, минут и секунд, но Excel может интерпретировать их как текст или другие типы.
В вашем случае, поскольку вы пытаетесь суммировать время, вам не нужно суммировать необработанные отметки часов. Вы хотите заменить столбцы времени первыми (самыми ранними) и последними (самыми последними) значениями для каждого конкретного дня для каждого человека.
Мы не суммируем записи времени, поэтому List.Sum неприменим.
= Table.Group(#"Filtered Pablo", {"Name", "Date"},
{{"AM_In", each List.Min([AM_In_raw]), type time},
{"AM_Out", each List.Max([AM_Out_raw]), type time},
{"PM_In", each List.Min([PM_In_raw]), type time},
{"PM_Out", each List.Max([PM_Out_raw]), type time}})
Я заменил List.Sum на List.Min и List.Max, которые возвращают наименьшее (первое) и наибольшее (последнее) значения в списке соответственно.
Посмотрите, работает ли вышеизложенное.
Это исправленный оператор, который я использовал: ` = Table.Group(#"Reordered Columns", {"Persona", "DateIn"}, {{"AM_In", каждый List.Max([AM_In_raw]), введите время с нулевым значением} , {"AM_Out", каждый List.Max([AM_Out_raw]), введите время, допускающее значение NULL}, {"PM_In", каждый List.Max([PM_In_raw]), введите время, допускающее значение NULL}, {"PM_Out", каждый List.Max ([PM_Out_raw]), введите обнуляемое время}}) ` Спасибо за помощь.
Здравствуйте @user24888148, спасибо за быстрый ответ. На предыдущих шагах я уже преобразовал текст во время и взял минимальное время «входа» и максимальное время «выхода». Я думаю, проблема была в том, что время нельзя суммировать. Я решил проблему, взяв максимум для каждой группы, поскольку значение, которое я хочу исключить, всегда #time(0,0,0) отображается как 00:00:00. Это утверждение, которое я использовал: