Страницы: 1
RSS
Группировка диапазонов времени, Поиск решения в Power Query
 
Доброго времени суток, форумчане!

Помогите пожалуйста с поиском решения задачи в Power Query, над которой ломаю мозг уже 2 дня, зациклился что-то.
Краткое описание:
Есть таблица из трех столбцов: № Дня, "Начало", "Окончание"
Загвоздка в том, что некоторые интервалы в разрезе одинакового дня пересекаются между собой, к примеру (начало-окончание):

Часы:минуты

08:10-08:55
09:00-10:15
09:30-10:50
11:20-11:50
11:05-11:40

Здесь видно, что интервал 9:30-10:50 пересекается с 09:00-10:15, т.е. должен получиться общий: 09:00-10:50,
так же и по нижним. Должно получится 11:05-11:50

Пример во вложении.
Через какие функции/формулы можно "победить" этот вопрос?
Заранее спасибо!
Изменено: RVA - 11.12.2018 15:11:11
 
Разрыв в интервале это секунда/минута?
В смысле 8:00:00 - 8:19:59 и 8:20:00 - 8:59:59
это один интервал или 2 разных?
Изменено: PooHkrd - 11.12.2018 15:11:13
Вот горшок пустой, он предмет простой...
 
Часы/минуты
Интервалы бывают разные (независимые и пересекающиеся). Если пересекаются, тогда из пересекающихся нужно сделать один общий, т.е. минимальное время из одного и максимальное из другого.
8:00:00 - 8:19:59 и 8:20:00 - 8:59:59 - это разные, поскольку идут один за другим последовательно.
 
Цитата
RVA написал:
Часы / минуты
Цитата
RVA написал:
8:00:00 - 8:19:59 и 8:20:00 - 8:59:59 - это разные
определитесь, пожалуйста с минимальной разницей между интервалами, от этого зависит как решать.
Вот горшок пустой, он предмет простой...
 
1 минута, секунды я отсеку
 
Цитата
RVA написал: решения задачи в Power Query,
Ваших попыток в PQ не обнаружил.
Чем не устраивает сводная?
"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
Z, Плохо просмотрели задание))
З.Ы. Самому интересно решение))  
В жизни нет ничего невозможного! Есть только недостаток знаний и умений.
 
Я решаю более глобальную задачу с обработкой отчетов. Решение полностью на PQ, но вот на этом моменте затупил. Да, в примере я просто описал смысл вопроса без вложения кода PQ, поскольку мой многочисленные попытки потерпели фиаско. Я пробовал группировки, многократную смещенную индексацию с сопоставлением предшествующих интервалов, но все-равно получается не то пальто. Зацикливать по кругу данные для сравнения думаю не вариант, т.к. вариации и кол-во  таких диапазонов в разрезе одного дня не предсказуемы. Нужно какое-то оригинальное и универсальное решение... Может кто-нибудь хотя бы идею подкинет и формулу (синтаксис) :)
 
Идея такая: для каждой строки через List.Durations генерим список из арифметической последовательности с шагом меньшим чем погрешность, для вашего случая это 30 секунд. Потом группируем и через List.Union объединяем получившиеся списки и удаляем дубликаты. дальше разворачиваем значения списков в строки и делаем столбец который определит разрывы между двумя соседними строчками больше 30 секунд, потом опять группировка и через List.Min/Max сшиваем новый цельный диапазон.
Если сами не сможете. то завтра попробую по-колдовать с вашей задачкой. Там в принципе должно быть не сложно, только муторно.
Изменено: PooHkrd - 11.12.2018 18:07:44
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал: Там в принципе должно быть не сложно, только муторно.
+!
Мо быть TC все-таки догадается, Правила пп 2.2, 2.3, предоставить более полно ИДЕЮ/ЦЕЛЬ всей заморочки, и, пожалуй, главное - приложит "файл(ы) с примером (общим весом не более 300Кб) в реальной структуре и форматах данных того, что есть сейчас и того, что хотелось бы на выходе.
Есть догадка/надежда, что временные диапазоны каким-то образом должны иметь привязку к чему- нибудь - имени, номеру, индексу и т.п. Потому как "пример-примитив" очень смахивает на контроль на проходной... ;)
"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
RVA,

Готово:
Код
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 - 11.12.2018 18:31:47
 
Aleksei_Zhigulin, интересное решение, но знаков = в сравнениях не хватает. ;)  Если время окончания одного периода совпадет со временем начала другого, то запрос выдаст не корректный результат. См. вложение.
И еще, шаг next было бы неплохо закинуть в буфер, с таким количеством обращений к таблице в предыдущем вычислении на серьезном массиве боюсь что запрос со своими ленивыми вычислениями может зависнуть. Но это мое чистое предположение.
Вот горшок пустой, он предмет простой...
 
Приветствую Всех!

Да, представленный Aleksei_Zhigulin вариант решения задачи не корректно срабатывает в случае других вариаций, НО! главное  - это идея использования такого алгоритма.
Aleksei_Zhigulin - огромное тебе спасибо!!!
Пришлось немного разобраться в логике и допилить под себя. В итоге задача решена!

PooHkrd - так же благодарю за поддержку и помощь!!!

Спасибо всем участникам форума и сайту PlanetaExcel !!!!!!

И еще..., может кто скинет ссылку на учебные материалы по PQ? Желательно русскоязычные c нормальным описанием формул и функций применительно к жизненным примерам, а не в таком формате как представлено на сайте Microsoft ..)

Всего доброго! С наступающим НГ :)
 
По русски только здесь 2 обзорных статьи. Плюс есть блог Максима Зеленского. И здесь переводные статьи Криса Вебба. Больше по PQ на русском ничего не встречал. Учите буржуйский - на нем океаны статей на все случаи жизни.
Изменено: PooHkrd - 12.12.2018 18:04:49
Вот горшок пустой, он предмет простой...
 
PooHkrd - Спасибо большое!


 
Доброе время суток.
Несколько другой вариант без
Цитата
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, как всегда, решение огонь! Утащил разбирать. Спасибо.
Вот горшок пустой, он предмет простой...
 
Андрей 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 - 14.12.2018 10:43:30
Вот горшок пустой, он предмет простой...
 
PooHkrd, Хорошо, по порядку:)
1. Что такое (sub) и зачем в функцию вставили let/in
2. ЗАчем через буфер мы ищем кол-во элементов?
3. Лично я, не понял, как работает Table.ToRecords (по справке не понятно)
В жизни нет ничего невозможного! Есть только недостаток знаний и умений.
 
Цитата
Александр написал:
1. Что такое (sub) и зачем в функцию вставили let/in
Изучаем
Цитата
Александр написал:
2. ЗАчем через буфер мы ищем кол-во элементов?
Для борьбы с "ленивыми" вычислениями - быстрее будет. И вы не внимательны, в List.Generate идёт последовательное обращение к элементам списка записей prev = base{i}, а не только определение числа строк таблицы ;) . Не стоит рассматривать код только как линейную последовательность - новый шаг как результат использования предыдущего шага. Вот она проблема мышиного хардкода :)
Цитата
Александр написал:
3. Лично я, не понял, как работает Table.ToRecords (по справке не понятно)
Преобразует таблицу в список записей.
Изменено: Андрей VG - 14.12.2018 11:11:57
 
Цитата
Андрей VG написал:
Изучаем
В этом и проблема :) для меня ин языки не поддаются изучению))
Цитата
Андрей VG написал:
И вы не внимательны
Дело куда проще, я просто не понимаю)))

И если последовательно проверяем base, а функция Table.ToRecords
Цитата
Андрей VG написал:
Преобразует таблицу в список записей.
т.е. как я понял, это 3 столбца преобразует в 1? То как он отличает "Начало" и "Конец"?  
В жизни нет ничего невозможного! Есть только недостаток знаний и умений.
 
Цитата
Александр написал: .е. как я понял, это 3 столбца преобразует в 1?
Неправильное предположение. Изучаем.
Страницы: 1
Наверх