Есть аудиторы, которые аудируют торговые точки, все их результаты попадают в систему, откуда выгружаются в плоскую таблицу, из которой рассчитываеются множественные KPI... Так вот есть загвоздка с одним из KPI, а именно подсчет уникальных проаудированных торговых точек по месяцам, с учетом того, что каждая точка внутри месяца, считается "Новой", если она не посещалась 2 и более месяца...
Что нужно, - создать с помощью PQ дополнительный столбец, в котором пометить все вхождения торговых точек "POS ID" в массиве данных по заданным параметрам - "единицей", если параметрам не отвечает, то "ноль", для дальнейшей работы с этим столбцом в сводных таблицах.
Если попутно у вас возникнут предложения и пожелания, как можно это сделать по другому, всегда рад обратной связи.
Если результат нужен в сводной таблице, то почему бы не загрузить результат запроса в модель данных и посчитать это дело в мере? Зачем городить дополнительные столбцы?
Для меня в этом запросе есть что по изучать с точки зрения операторов и логики запроса - но это хорошо) 2 момента: 1. После запроса пропал столбец AUDIT ID - он нужен как ключ в дальнейшем 2. Строк стало меньше на выходе, почему?
А так в целом, очень даже похоже на то что нужно. Честно говоря,думал, что будет как то проще запрос выглядеть(
tdolgiy написал: 1. После запроса пропал столбец AUDIT ID - он нужен как ключ в дальнейшем 2. Строк стало меньше на выходе, почему?
По условию в файле
Цитата
1. Удалить дубликаты точек [POS ID] внутри одного месяца [Data of Creation]
Его не выполнить, если не исключить AUDIT ID - вместе с ним строки уникальные Если же оставлять, то крутить придётся серьёзнее для поиска предстоящей даты вне месяца, в случае равных дат одинаковых POS ID, по крайней мере в рамках представленного алгоритма. В общем попробуйте довести до ума.
tdolgiy написал: Осталось понять как это все работает)
Это-то дело параллельное - в рамках изучения Power Query. У вас сейчас ещё не пройден этап постановки задачи, например
Цитата
1. Удалить дубликаты точек [POS ID] внутри одного месяца [Data of Creation]
С логической точки зрения это означает, что внутри одного массива дат месяц.год не может быть более одного POS ID с уникальным значением. Вы действительно этого хотите? Не продуман следующий - алгоритмизации ей решения на логическом уровне.
Андрей VG, Вы не обращайте особо внимание на 1й пункт, в этом месте я писал себе логику действий в PQ, для того чтобы из этого массива посчитать то, что вы мне помогли. Видимо забыл удалить его с листа...( Ну исходя из задачи, сопоставить прирост новых точек по месяцам, POS ID и должен быть уникальным в рамках месяц.год. По крайней мере, таблицу план факт я построил.
sanych09 написал: а можно было это все по шагово выполнить через интерфейс программы?
Частично, да. Разбить задачу на подзадачи, для этих подзадач сформировать данные, прощёлкать каждую подзадачу и оформить функцией, сформировать конечный запрос, как цепочку вызовов этих функций. Хотя всё равно - немножко ручками поработать придётся. Ну, не спец я в мышином хардкоде. Вполне возможно, если выбрать другой алгоритм, то можно и полностью по кнопкам (может с минимумом на Table.NestedJoin, чтобы была возможность соединить две таблицы через интерфейс). Но будет ли это худо-бедно оптимально? Привыкнут кнопками, а потом когда Android на сотовом трёхгодичной давности начинает нещадно тормозить, так в крик по форумам
Да там вообще все шаги "кнопочные". Из ручного только преобразование запроса в функцию и вызов её при группировке. Ну, еще вместо Join придется NestedJoin использовать с разворачиванием. Ах, да, удаление столбца с дубликатами сразу в один шаг завернули. А вообще, кстати, можно и без группировки обойтись, тогда вообще все кнопками получится, только сортировку, которая внутри функции делается придется по двум столбцам делать - добавить POS ID, и джойнить потом не только по индексу, а еще и по POS ID, тогда, в принципе получится все тоже самое.
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
#"Другие удаленные столбцы"
Алексей, спасибо, что нашли время на "Доклацанье") В целом из запроса Андрея, я наверное уловил ту же логику... Зато мне сейчас четко понятно, что я ничего не знаю про объединение таблиц, и теряю логику происходящего волшебства на моменте разворота таблицы... А до и после - все понятно
Пойду еще по изучаю, и попробую воссоздать ваши шаги с нуля...
И еще, формулу добавления пользовательского столбца в ручную писали или в новой версии PQ есть возможность задавать условие?
Всем еще раз спасибо за помощь, интересно именно разобраться как устроена логика построения запроса!!!
tdolgiy написал: В целом из запроса Андрея, я наверное уловил ту же логику...
так она и есть та же, только вместо группировки используется сортировка по двум столбцам и джойн по двум же.
Цитата
tdolgiy написал: и теряю логику происходящего волшебства на моменте разворота таблицы...
а что именно не понятно в развороте? присоединили таблицу саму к себе, но ключами выступают разные столбцы индексов, таким образом вы выводите в строку к дате значение предыдущего посещения в рамках одного POS ID
Цитата
tdolgiy написал: формулу добавления пользовательского столбца в ручную писали
да, в принципе можно и здесь можно обойтись только мышкой и использовать кнопку добавления условного столбца, но тогда перед этим пришлось бы создавать столбец из разности [Date of creation] - [След.Date of creation], после чего преобразовывать его из типа duration в тип number.
Цитата
tdolgiy написал: как устроена логика построения запроса
когда начинал, то для построения алгоритма использовал традиционные средства Экселя формулы и сводные. Потом реализовывал эти шаги при помощи кнопок PQ, если чего не получалось спрашивал тут. Когда немножко разобрался с синтаксисом бросил это дело, ибо язык М позволяет многие вещи делать проще, да и голова уже стала мыслить не в категории ячеек, а в категориях строк, столбцов, таблиц и списков.