Страницы: 1
RSS
Группировка по столбцам с дополнительными условиями в PowerQuery
 
PowerQuery предоставляет неплохой инструмент группирования данных. Однако на практике часто приходится использовать дополнительные условиях на группировку. Так, например, имеется файл с информацией о безбилетных пассажирах, содержащая следующие столбцы (файл прилагается):
- дата установления факта безбилетного проезда;
- время  установления факта безбилетного проезда,
- остановочный пункт, на котором установлен факт безбилетного проезда;
- номер маршрута, в котором обнаружен безбилетник;
- табельный номер работника, обнаружевшего безбилетника.

Необходимо сгруппировать информацию ПО КАЖДОЙ ПРОВЕРКЕ так, что бы получились столбцы:
- Дата
- Остановочный пункт
- время начала проверки на остановочном пункте
- время окончания проверки на остановочном пункте
- суммарное количество безбилетников;
- список маршрутов
- список табельных номеров.

При этом известно, что больше трех часов в подряд проверка на одной остановке не производится. То есть, если на какой то остановке установлен факт безбилетного проезда в 8:00, а потом, например, в 17:00 этого же дня, то это были разные проверки, и группировать их вместе нельзя. То есть надо при группировке добавить проверку условия, что б между каждой парой фактов установленного на одной и той же остановке безбилетного проезда  было не более трех часов. А если больше трех часов, то это уже другая проверка, т.е. следующая строка в сгруппированной таблице.
 
а почему в Курилке, а не в общем разделе?
 
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    types = Table.TransformColumnTypes(Source,{{"Дата", type date}, {"Время", type time}, {"маршрут", type text}, {"таб", type text}}),
    sorted = Table.Sort(types,{{"Остановочный пункт", Order.Ascending}, {"Дата", Order.Ascending}, {"Время", Order.Ascending}}),
    gr = 
        Table.Group(
            sorted, {"Дата", "Время", "Остановочный пункт"}, 
            {{"начало", each List.Min([Время])}, 
            {"окончание", each List.Max([Время])},
            {"кол-во", List.Count},
            {"маршруты", each Text.Combine(List.Distinct([маршрут]), ", ")},
            {"таб номера", each Text.Combine(List.Distinct([таб]), ", ")}}, 
            GroupKind.Local, 
            (s, c) => 
                Number.From(
                    (Duration.Hours(c[Время] - s[Время]) > 3) or
                    (s[Дата] <> c[Дата]) or 
                    (s[Остановочный пункт] <> c[Остановочный пункт])
                )
        ),
    out = Table.RemoveColumns(gr,{"Время"})
in
    out
Пришелец-прораб.
 
Спасибо за помощь.

Но почему то на шаге gr выдается ошибка в поле "кол-во". С чем это связано?
 
Цитата
Сергей Аземша написал:
С чем это связано?
на тестовых данных ошибки нет. Файл прикладываю. Результат не выгружал, т.к. превышу лимит на размер файла - выгрузите самостоятельно. А какая ошибка выдается?
Попробуйте еще вот такой вариант
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    types = Table.TransformColumnTypes(Source,{{"Дата", type date}, {"Время", type time}, {"маршрут", type text}, {"таб", type text}}),
    sorted = Table.Sort(types,{{"Остановочный пункт", Order.Ascending}, {"Дата", Order.Ascending}, {"Время", Order.Ascending}}),
    gr = 
        Table.Group(
            sorted, {"Дата", "Время", "Остановочный пункт"}, 
            {{"начало", each List.Min([Время])}, 
            {"окончание", each List.Max([Время])},
            {"кол-во", each Table.RowCount(_)},
            {"маршруты", each Text.Combine(List.Distinct([маршрут]), ", ")},
            {"таб номера", each Text.Combine(List.Distinct([таб]), ", ")}}, 
            GroupKind.Local, 
            (s, c) => 
                Number.From(
                    (Duration.Hours(c[Время] - s[Время]) > 3) or
                    (s[Дата] <> c[Дата]) or 
                    (s[Остановочный пункт] <> c[Остановочный пункт])
                )
        ),
    out = Table.RemoveColumns(gr,{"Время"})
in
    out
Изменено: Alien Sphinx - 07.06.2023 13:41:47 (вариант кода с Table.RowCount)
Пришелец-прораб.
 
Цитата
New написал: а почему в Курилке
Паша, это сделано специально для того, чтобы как можно меньше людей обратили внимание на тему.
 
С новым кодом все работает. Только, не пойму, результат куда сохраняет?
 
А, все, понял...

Все работает..

Спасибо огромное)
 
Написал в курилке потому, что не понял как написать в положенном месте... видимо плохо разобрался с тем, как форум работает
 
Цитата
Сергей Аземша написал:
Все работает..
некорректно отрабатывается "время более 3 часов". Замените код на вот этот
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    types = Table.TransformColumnTypes(Source,{{"Дата", type date}, {"Время", type time}, {"маршрут", type text}, {"таб", type text}}),
    sorted = Table.Sort(types,{{"Остановочный пункт", Order.Ascending}, {"Дата", Order.Ascending}, {"Время", Order.Ascending}}),
    gr = 
        Table.Group(
            sorted, {"Дата", "Время", "Остановочный пункт"}, 
            {{"начало", each List.Min([Время])}, 
            {"окончание", each List.Max([Время])},
            {"кол-во", each Table.RowCount(_)},
            {"маршруты", each Text.Combine(List.Distinct([маршрут]), ", ")},
            {"таб номера", each Text.Combine(List.Distinct([таб]), ", ")}}, 
            GroupKind.Local, 
            (s, c) => 
                Number.From(
                    ((c[Время] - s[Время]) > #duration(0, 3, 0, 0)) or
                    (s[Дата] <> c[Дата]) or 
                    (s[Остановочный пункт] <> c[Остановочный пункт])
                )
        ),
    out = Table.RemoveColumns(gr,{"Время"})
in
    out
Изменено: Alien Sphinx - 07.06.2023 19:27:20
Пришелец-прораб.
 
Добрый день,

да, действительно немножко не корректно обрабатывало. Спасибо, что подправили...

Еще раз огромнейшее спасибо за помощь.
Страницы: 1
Наверх