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

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

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

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

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

Заранее спасибо всем откликнувшимся!
Изменено: tdolgiy - 5 Сен 2018 12:45:49
 
Если результат нужен в сводной таблице, то почему бы не загрузить результат запроса в модель данных и посчитать это дело в мере? Зачем городить дополнительные столбцы?
 
это без разницы куда выгрузить, главное выгрузить то, что надо)
Изменено: tdolgiy - 7 Сен 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, по крайней мере в рамках представленного алгоритма. В общем попробуйте довести до ума.
doubleDate.png (2.22 КБ)
Изменено: Андрей VG - 5 Сен 2018 22:10:12
 
Андрей VG, спасибо огромное!

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

Спасибо
 
Цитата
tdolgiy написал:
Осталось понять как это все работает)
Это-то дело параллельное - в рамках изучения Power Query.
У вас сейчас ещё не пройден этап постановки задачи, например
Цитата
1. Удалить дубликаты точек [POS ID] внутри одного месяца [Data of Creation]
С логической точки зрения это означает, что внутри одного массива дат месяц.год не может быть более одного POS ID с уникальным значением. Вы действительно этого хотите?
Не продуман следующий - алгоритмизации ей решения на логическом уровне.
 
Андрей VG, Вы не обращайте особо внимание на 1й пункт, в этом месте я писал себе логику действий в PQ, для того чтобы из этого массива посчитать то, что  вы мне помогли. Видимо забыл удалить его с листа...(
Ну исходя из задачи, сопоставить прирост новых точек по месяцам, POS ID и должен быть уникальным в рамках месяц.год. По крайней мере, таблицу план факт я построил.
Снимок.JPG (25.33 КБ)
 
Андрей VG, сложненько. а можно было это все по шагово выполнить через интерфейс программы?
Опыт и практика - великое дело! Век живи, Век учись!
 
Цитата
sanych09 написал:
а можно было это все по шагово выполнить через интерфейс программы?
Частично, да. Разбить задачу на подзадачи, для этих подзадач сформировать данные, прощёлкать каждую подзадачу и оформить функцией, сформировать конечный запрос, как цепочку вызовов этих функций. Хотя всё равно - немножко ручками поработать придётся. Ну, не спец я в мышином хардкоде.
Вполне возможно, если выбрать другой алгоритм, то можно и полностью по кнопкам (может с минимумом на Table.NestedJoin, чтобы была возможность соединить две таблицы через интерфейс). Но будет ли это худо-бедно оптимально?
Привыкнут кнопками, а потом когда Android на сотовом трёхгодичной давности начинает нещадно тормозить, так в крик по форумам  :D
Изменено: Андрей VG - 6 Сен 2018 14:25:03
 
Да там вообще все шаги "кнопочные". Из ручного только преобразование запроса в функцию и вызов её при группировке. Ну, еще вместо Join придется NestedJoin использовать с разворачиванием. Ах, да, удаление столбца с дубликатами сразу в один шаг завернули.
А вообще, кстати, можно и без группировки обойтись, тогда вообще все кнопками получится, только сортировку, которая внутри функции делается придется по двум столбцам делать - добавить POS ID, и джойнить потом не только по индексу, а еще и по POS ID, тогда, в принципе получится все тоже самое.
Изменено: PooHkrd - 6 Сен 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 - 6 Сен 2018 16:50:13
 
Вторая строка уже была в исходнике - добавил первую и результаты у запросов идентичные. А что не так?
Изменено: PooHkrd - 6 Сен 2018 17:16:16
 
Цитата
PooHkrd написал:
А что не так?
Приношу свои извинения. Показалось, что Join только по индексу.
 
Ну и ладненько, я то я уж заволновался. :D
 
Алексей, спасибо, что нашли время на "Доклацанье")
В целом из запроса Андрея, я наверное уловил ту же логику...
Зато мне сейчас четко понятно, что я ничего не знаю про объединение таблиц, и теряю логику происходящего волшебства на моменте разворота таблицы...
А до и после - все понятно :D

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

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

Всем еще раз спасибо за помощь, интересно именно разобраться как устроена логика построения запроса!!!
Изменено: tdolgiy - 6 Сен 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
Читают тему (гостей: 1)
Наверх