Страницы: 1
RSS
Power Query. Подсчет входящих элементов в столбце по заданным условиям
 
Коллеги, добрый день!

Есть такой кейс:

Есть аудиторы, которые аудируют торговые точки, все их результаты попадают в систему, откуда выгружаются в плоскую таблицу, из которой  рассчитываеются множественные KPI... Так вот есть загвоздка с одним из KPI, а именно подсчет уникальных проаудированных торговых точек по месяцам, с учетом того, что каждая точка внутри месяца, считается "Новой", если она не посещалась 2 и более месяца...

Что нужно, - создать с помощью  PQ дополнительный столбец, в котором пометить все вхождения торговых точек "POS ID" в массиве данных  по заданным параметрам - "единицей", если параметрам не отвечает, то "ноль", для дальнейшей работы с этим столбцом в сводных таблицах.

Если попутно у вас возникнут предложения и пожелания, как можно это  сделать по другому, всегда рад обратной связи.

Заранее спасибо всем откликнувшимся!
Изменено: tdolgiy - 05.09.2018 12:45:49
 
Если результат нужен в сводной таблице, то почему бы не загрузить результат запроса в модель данных и посчитать это дело в мере? Зачем городить дополнительные столбцы?
Вот горшок пустой, он предмет простой...
 
это без разницы куда выгрузить, главное выгрузить то, что надо)
Изменено: tdolgiy - 07.09.2018 12:00:34
 
tdolgiy, кнопка цитирования не для ответа! Да и не цитата это, а копия сообщения. Зачем?
 
Доброе время суток
Возможно как-то так.
 
Андрей VG, спасибо большое!

Для меня в этом запросе есть что по изучать с точки зрения операторов и логики запроса - но это хорошо)
2 момента:
1. После запроса пропал столбец AUDIT ID - он нужен как ключ в дальнейшем
2. Строк стало меньше на выходе, почему?

А так в целом, очень даже похоже на то что нужно. Честно говоря,думал, что будет как то проще запрос выглядеть(
 
Цитата
tdolgiy написал:
1. После запроса пропал столбец AUDIT ID - он нужен как ключ в дальнейшем
2. Строк стало меньше на выходе, почему?
По условию в файле
Цитата
1. Удалить дубликаты точек [POS ID] внутри одного месяца [Data of Creation]
Его не выполнить, если не исключить AUDIT ID - вместе с ним строки уникальные ;)
Если же оставлять, то крутить придётся серьёзнее для поиска предстоящей даты вне месяца, в случае равных дат одинаковых POS ID, по крайней мере в рамках представленного алгоритма. В общем попробуйте довести до ума.
Изменено: Андрей VG - 05.09.2018 22:10:12
 
Андрей VG, спасибо огромное!

Осталось понять как это все работает)
Пошел разбираться.

Спасибо
 
Цитата
tdolgiy написал:
Осталось понять как это все работает)
Это-то дело параллельное - в рамках изучения Power Query.
У вас сейчас ещё не пройден этап постановки задачи, например
Цитата
1. Удалить дубликаты точек [POS ID] внутри одного месяца [Data of Creation]
С логической точки зрения это означает, что внутри одного массива дат месяц.год не может быть более одного POS ID с уникальным значением. Вы действительно этого хотите?
Не продуман следующий - алгоритмизации ей решения на логическом уровне.
 
Андрей VG, Вы не обращайте особо внимание на 1й пункт, в этом месте я писал себе логику действий в PQ, для того чтобы из этого массива посчитать то, что  вы мне помогли. Видимо забыл удалить его с листа...(
Ну исходя из задачи, сопоставить прирост новых точек по месяцам, POS ID и должен быть уникальным в рамках месяц.год. По крайней мере, таблицу план факт я построил.
 
Андрей VG, сложненько. а можно было это все по шагово выполнить через интерфейс программы?
Опыт и практика - великое дело! Век живи, Век учись!
 
Цитата
sanych09 написал:
а можно было это все по шагово выполнить через интерфейс программы?
Частично, да. Разбить задачу на подзадачи, для этих подзадач сформировать данные, прощёлкать каждую подзадачу и оформить функцией, сформировать конечный запрос, как цепочку вызовов этих функций. Хотя всё равно - немножко ручками поработать придётся. Ну, не спец я в мышином хардкоде.
Вполне возможно, если выбрать другой алгоритм, то можно и полностью по кнопкам (может с минимумом на Table.NestedJoin, чтобы была возможность соединить две таблицы через интерфейс). Но будет ли это худо-бедно оптимально?
Привыкнут кнопками, а потом когда Android на сотовом трёхгодичной давности начинает нещадно тормозить, так в крик по форумам  :D
Изменено: Андрей VG - 06.09.2018 14:25:03
 
Да там вообще все шаги "кнопочные". Из ручного только преобразование запроса в функцию и вызов её при группировке. Ну, еще вместо Join придется NestedJoin использовать с разворачиванием. Ах, да, удаление столбца с дубликатами сразу в один шаг завернули.
А вообще, кстати, можно и без группировки обойтись, тогда вообще все кнопками получится, только сортировку, которая внутри функции делается придется по двум столбцам делать - добавить POS ID, и джойнить потом не только по индексу, а еще и по POS ID, тогда, в принципе получится все тоже самое.
Изменено: PooHkrd - 06.09.2018 14:32:21
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
Да там вообще все шаги "кнопочные".
Привет, Алексей.
Доклацаете до полного решения задачи ТС? ;)
 
Не до того щас. работы вагон. Закладку поставил себе, глядишь на досуге поклацаю.
Вот горшок пустой, он предмет простой...
 
PooHkrd,Было бы интересно! Если найдете время, будьте добры, для полного понимания процесса.

Андрей VG, Ну в целом, разбираюсь помаленьку с кодом - операторов сложных нет, но вот не знание синтаксиса, немного выбивает меня из колеи - ну тем интереснее)

А вообще, если бы я понял логику запроса, "На пальцах", что мы делаем в каждой строке - думаю жизнь стала бы легче. Я попозже напишу как я понял, а вы поправьте плиз, что не верно.

Всем спасибо за проявленный интерес!
 
В общем вот вариант кода, который полностью сделан на кнопочках, все названия шагов автоматические, думаю сами разберетесь какими кнопками какие шаги создаются. Если что - вопрошайте.
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица4"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Date of creation", type date}, {"POS ID", type text}}),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Измененный тип",{"Audit ID"}),
    #"Удаленные дубликаты" = Table.Distinct(#"Удаленные столбцы"),
    #"Сортированные строки" = Table.Sort(#"Удаленные дубликаты",{{"POS ID", Order.Ascending}, {"Date of creation", Order.Ascending}}),
    #"Добавлен индекс" = Table.AddIndexColumn(#"Сортированные строки", "Индекс", 0, 1),
    #"Добавлен индекс1" = Table.AddIndexColumn(#"Добавлен индекс", "Индекс.1", 1, 1),
    #"Объединенные запросы" = Table.NestedJoin(#"Добавлен индекс1",{"Индекс", "POS ID"},#"Добавлен индекс1",{"Индекс.1", "POS ID"},"Пред",JoinKind.LeftOuter),
    #"Развернутый элемент След" = Table.ExpandTableColumn(#"Объединенные запросы", "Пред", {"Date of creation"}, {"След.Date of creation"}),
    #"Добавлен пользовательский объект" = Table.AddColumn(#"Развернутый элемент След", "KPI", each if [След.Date of creation] is null then 1 else if Duration.TotalDays([Date of creation] - [След.Date of creation]) > 60 then 1 else 0, Int64.Type),
    #"Другие удаленные столбцы" = Table.SelectColumns(#"Добавлен пользовательский объект",{"Date of creation", "POS ID", "KPI"})
in
    #"Другие удаленные столбцы"
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
который полностью сделан на кнопочках
Замечательно!
Но, что если в исходной таблице будет
28.03.2018 35644
и
28.04.2018 35669
?
Изменено: Андрей VG - 06.09.2018 16:50:13
 
Вторая строка уже была в исходнике - добавил первую и результаты у запросов идентичные. А что не так?
Изменено: PooHkrd - 06.09.2018 17:16:16
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
А что не так?
Приношу свои извинения. Показалось, что Join только по индексу.
 
Ну и ладненько, я то я уж заволновался. :D
Вот горшок пустой, он предмет простой...
 
Алексей, спасибо, что нашли время на "Доклацанье")
В целом из запроса Андрея, я наверное уловил ту же логику...
Зато мне сейчас четко понятно, что я ничего не знаю про объединение таблиц, и теряю логику происходящего волшебства на моменте разворота таблицы...
А до и после - все понятно :D

Пойду еще по изучаю, и попробую воссоздать ваши шаги с нуля...

И еще, формулу добавления пользовательского столбца в ручную писали или в новой версии PQ есть возможность задавать условие?

Всем еще раз спасибо за помощь, интересно именно разобраться как устроена логика построения запроса!!!
Изменено: tdolgiy - 06.09.2018 23:18:39
 
Цитата
tdolgiy написал:
В целом из запроса Андрея, я наверное уловил ту же логику...
так она и есть та же, только вместо группировки используется сортировка по двум столбцам и джойн по двум же.
Цитата
tdolgiy написал:
и теряю логику происходящего волшебства на моменте разворота таблицы...
а что именно не понятно в развороте? присоединили таблицу саму к себе, но ключами выступают разные столбцы индексов, таким образом вы выводите в строку к дате значение предыдущего посещения в рамках одного POS ID
Цитата
tdolgiy написал:
формулу добавления пользовательского столбца в ручную писали
да, в принципе можно и здесь можно обойтись только мышкой и использовать кнопку добавления условного столбца, но тогда перед этим пришлось бы создавать столбец из разности [Date of creation] - [След.Date of creation], после чего преобразовывать его из типа duration в тип number.
Цитата
tdolgiy написал:
как устроена логика построения запроса
когда начинал, то для построения алгоритма использовал традиционные средства Экселя формулы и сводные. Потом реализовывал эти шаги при помощи кнопок PQ, если чего не получалось спрашивал тут. Когда немножко разобрался с синтаксисом бросил это дело, ибо язык М позволяет многие вещи делать проще, да и голова уже стала мыслить не в категории ячеек, а в категориях строк, столбцов, таблиц и списков.
Вот горшок пустой, он предмет простой...
 
PooHkrd, не знаю как тут лайк поставить, но все расписано мега детально! Лайк!  :D  
Опыт и практика - великое дело! Век живи, Век учись!
 
PooHkrd,спасибо, за столь подробное объяснение!  8)

sanych09, да, лайков и спасибо на этом форуме не хватает)
 
sanych09, tdolgiy, а простого человеческого СПАСИБО, набранного с клавиатуры, недостаточно? Это я про лайки...
Страницы: 1
Наверх