Помогите пожалуйста с поиском решения задачи в Power Query, над которой ломаю мозг уже 2 дня, зациклился что-то. Краткое описание: Есть таблица из трех столбцов: № Дня, "Начало", "Окончание" Загвоздка в том, что некоторые интервалы в разрезе одинакового дня пересекаются между собой, к примеру (начало-окончание):
Здесь видно, что интервал 9:30-10:50 пересекается с 09:00-10:15, т.е. должен получиться общий: 09:00-10:50, так же и по нижним. Должно получится 11:05-11:50
Пример во вложении. Через какие функции/формулы можно "победить" этот вопрос? Заранее спасибо!
Часы/минуты Интервалы бывают разные (независимые и пересекающиеся). Если пересекаются, тогда из пересекающихся нужно сделать один общий, т.е. минимальное время из одного и максимальное из другого. 8:00:00 - 8:19:59 и 8:20:00 - 8:59:59 - это разные, поскольку идут один за другим последовательно.
Я решаю более глобальную задачу с обработкой отчетов. Решение полностью на PQ, но вот на этом моменте затупил. Да, в примере я просто описал смысл вопроса без вложения кода PQ, поскольку мой многочисленные попытки потерпели фиаско. Я пробовал группировки, многократную смещенную индексацию с сопоставлением предшествующих интервалов, но все-равно получается не то пальто. Зацикливать по кругу данные для сравнения думаю не вариант, т.к. вариации и кол-во таких диапазонов в разрезе одного дня не предсказуемы. Нужно какое-то оригинальное и универсальное решение... Может кто-нибудь хотя бы идею подкинет и формулу (синтаксис)
Идея такая: для каждой строки через List.Durations генерим список из арифметической последовательности с шагом меньшим чем погрешность, для вашего случая это 30 секунд. Потом группируем и через List.Union объединяем получившиеся списки и удаляем дубликаты. дальше разворачиваем значения списков в строки и делаем столбец который определит разрывы между двумя соседними строчками больше 30 секунд, потом опять группировка и через List.Min/Max сшиваем новый цельный диапазон. Если сами не сможете. то завтра попробую по-колдовать с вашей задачкой. Там в принципе должно быть не сложно, только муторно.
PooHkrd написал: Там в принципе должно быть не сложно, только муторно.
+! Мо быть TC все-таки догадается, Правила пп 2.2, 2.3, предоставить более полно ИДЕЮ/ЦЕЛЬ всей заморочки, и, пожалуй, главное - приложит "файл(ы) с примером (общим весом не более 300Кб) в реальной структуре и форматах данных того, что есть сейчас и того, что хотелось бы на выходе. Есть догадка/надежда, что временные диапазоны каким-то образом должны иметь привязку к чему- нибудь - имени, номеру, индексу и т.п. Потому как "пример-примитив" очень смахивает на контроль на проходной...
let
Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
types = Table.TransformColumnTypes(Source,{{"День", Int64.Type}, {"Начало", type time}, {"Оконч", type time}}),
sorting = Table.Sort(types,{{"День", Order.Ascending}, {"Начало", Order.Ascending}}),
prev = Table.AddIndexColumn(sorting, "Prev", -1, 1),
next = Table.AddIndexColumn(prev, "Next", 1, 1),
test = Table.AddColumn(next, "test", each if try [День] <> next[День]{[Prev]} otherwise true then "s->"
else if [Начало] > next[Оконч]{[Prev]}
then if try [Оконч] < next[Начало]{[Next]} otherwise true then "<-s-f"
else if [День] = next[День]{[Next]} then "s->" else "s-f"
else if [Оконч] > next[Оконч]{[Prev]} then "<-f" else "-"),
start = Table.AddColumn(test, "start", each if Text.StartsWith([test], "s") then [Начало] else null, type time),
finish = Table.AddColumn(start, "finish", each if Text.EndsWith([test], "f") then [Оконч] else null, type time),
#"Filled Down" = Table.FillDown(finish,{"start"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each Text.Contains([test], "f"))
in
#"Filtered Rows"
Aleksei_Zhigulin, интересное решение, но знаков = в сравнениях не хватает. Если время окончания одного периода совпадет со временем начала другого, то запрос выдаст не корректный результат. См. вложение. И еще, шаг next было бы неплохо закинуть в буфер, с таким количеством обращений к таблице в предыдущем вычислении на серьезном массиве боюсь что запрос со своими ленивыми вычислениями может зависнуть. Но это мое чистое предположение.
Да, представленный Aleksei_Zhigulin вариант решения задачи не корректно срабатывает в случае других вариаций, НО! главное - это идея использования такого алгоритма. Aleksei_Zhigulin - огромное тебе спасибо!!! Пришлось немного разобраться в логике и допилить под себя. В итоге задача решена!
PooHkrd - так же благодарю за поддержку и помощь!!!
Спасибо всем участникам форума и сайту PlanetaExcel !!!!!!
И еще..., может кто скинет ссылку на учебные материалы по PQ? Желательно русскоязычные c нормальным описанием формул и функций применительно к жизненным примерам, а не в таком формате как представлено на сайте Microsoft ..)
По русски только здесь 2 обзорных статьи. Плюс есть блог Максима Зеленского. И здесь переводные статьи Криса Вебба. Больше по PQ на русском ничего не встречал. Учите буржуйский - на нем океаны статей на все случаи жизни.
PooHkrd написал: с таким количеством обращений к таблице в предыдущем вычислении на серьезном массиве боюсь что запрос со своими ленивыми вычислениями может зависнуть
Код
let
Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"День", Int64.Type}, {"Начало", type time}, {"Оконч", type time}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"День", Order.Ascending}, {"Начало", Order.Ascending}}),
grouped = Table.Group(#"Sorted Rows", {"День"}, {"temp", (sub) =>
let
base = List.Buffer(Table.ToRecords(sub[[Начало], [Оконч]])),
listCount = List.Count(base),
maker = List.Generate(
() => [i = 0, prev = base{i}, Начало = prev[Начало], Оконч = prev[Оконч]],
each [i] < listCount,
each [i = [i] + 1, prev = base{i}, Начало = (if prev[Начало] <= [Оконч] then [Начало] else prev[Начало]), Оконч = (if prev[Оконч] > [Оконч] then prev[Оконч] else [Оконч])],
each [[Начало], [Оконч]]
),
stat = Table.Group(Table.FromRecords(maker), {"Начало"}, {"Оконч", each List.Max([Оконч])})
in
stat
}),
#"Expanded {0}" = Table.ExpandTableColumn(grouped, "temp", {"Начало", "Оконч"}, {"Начало", "Оконч"})
in
#"Expanded {0}"
Андрей VG, Приветствую! А можно Вас попросить (как и PooHkrd, и Максима Зеленского), комментировать шаги в коде? (как в VBA? через "//" ; /*текст*/) я для себя просматриваю от всех решения - учусь типа))) Буду очень благодарен. Мне сейчас не понятно строки
Код
grouped = Table.Group(#"Sorted Rows", {"День"}, {"temp", (sub) =>
let
base = List.Buffer(Table.ToRecords(sub[[Начало], [Оконч]])),
listCount = List.Count(base),
maker = List.Generate(
() => [i = 0, prev = base{i}, Начало = prev[Начало], Оконч = prev[Оконч]],
each [i] < listCount,
each [i = [i] + 1, prev = base{i}, Начало = (if prev[Начало] <= [Оконч] then [Начало] else prev[Начало]), Оконч = (if prev[Оконч] > [Оконч] then prev[Оконч] else [Оконч])],
each [[Начало], [Оконч]]
),
stat = Table.Group(Table.FromRecords(maker), {"Начало"}, {"Оконч", each List.Max([Оконч])})
in
stat
}),
В жизни нет ничего невозможного! Есть только недостаток знаний и умений.
Александр, а что конкретно не понятно? Table.Group изолирует таблицы в разрезе дней, а внутри анонимной функции при помощи List.Generate в цикле сравниваются значения из столбцов Начало и Окончания, чтобы определить пересекающиеся диапазоны. В тонкостях как там это Андрей организовал не расскажу, я пока с этой функцией не на столько дружу, но общий смысл его действий мне понятен. Алгоритм действий примерно такой же как у Aleksei_Zhigulin, только метод выбран более другой.
PooHkrd, Хорошо, по порядку:) 1. Что такое (sub) и зачем в функцию вставили let/in 2. ЗАчем через буфер мы ищем кол-во элементов? 3. Лично я, не понял, как работает Table.ToRecords (по справке не понятно)
В жизни нет ничего невозможного! Есть только недостаток знаний и умений.
Александр написал: 2. ЗАчем через буфер мы ищем кол-во элементов?
Для борьбы с "ленивыми" вычислениями - быстрее будет. И вы не внимательны, в List.Generate идёт последовательное обращение к элементам списка записей prev = base{i}, а не только определение числа строк таблицы . Не стоит рассматривать код только как линейную последовательность - новый шаг как результат использования предыдущего шага. Вот она проблема мышиного хардкода
Цитата
Александр написал: 3. Лично я, не понял, как работает Table.ToRecords (по справке не понятно)