Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Группировка строк по последней цене
 
#1
22.01.2018 15:48:14
Доброго времени суток!
Возможно не правильно сформулировал вопрос в теме - расширю в описании.
Есть несколько таблиц excel, которые объединяю/правлю запросом PQ.
Получаю в PQ таблицу из нескольких столбцов: Номенклатура, Цена, Валюта, Поставщик, Дата регистрации цены и др.
Для каждой номенклатуры множество цен во времени и по поставщикам.
Цель получить на выходе таблицу, в которой будет для каждой номенклатуры только последняя цена по дате (список столбцов должен остаться полный).

Поиск по форуму и в интернете меня отправляет к группировке, но максимум что я выжал - это получить таблицу из 2 столбцов (номенклатура и максимальная дата), остальные столбцы группировкой отсеиваются...если я их начинаю группировать тоже, то значение номенклатуры перестает быть уникальным
 
 
 
#2
22.01.2018 16:06:26
Если на словах, то делаете группировку по коду номенклатуры, а даты в значения с агрегатором МАКС. Потом получившуюся таблицу джойним к исходной по двум ключевым столбцам, и в образовавшемся столбце ставим фильтр <> null.
З.Ы. Сделал по-другому, по мотивам недавнего примера от Андрея VG.
Можно использовать функцию группировки для того чтобы применять какую-то функцию к группам строк по определенному критерию, вот и применил фильтр по максимальной дате прямо внутри группировки. Получилось красиво, прям самому нравится!
Как-то так.
Изменено: PooHkrd - 22.01.2018 16:20:01
Вот горшок пустой, он предмет простой...
 
 
 
#3
22.01.2018 16:28:00
Доброе время суток.
PooHkrd, можно и чуть по другому, в данном случае.
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Код номенклатуры", Int64.Type}, {"Дата", type date}, {"Цена", Int64.Type}}),
    #"Сгруппированные строки" = Table.Group(#"Измененный тип", {"Код номенклатуры"}, {{"Табла", each Table.Max(_, "Дата"), type record}}),
    #"Expanded {0}" = Table.ExpandRecordColumn(#"Сгруппированные строки", "Табла", {"Дата", "Цена"}, {"Дата", "Цена"})
in
    #"Expanded {0}"
 
 
 
#4
22.01.2018 16:30:44
Да, можно, не спорю. Только я сейчас перехожу пока на полухардкорный режим  :D . Беру кнопочные решения и комбинирую их вручную. Как руку набью, буду уже больше по справочникам лазить и некнопочные функции пользовать.
Вот горшок пустой, он предмет простой...
 
 
 
#5
22.01.2018 16:34:09
Я дошел до "Джойним" - понимаю что это, но не пойму где эта кнопка в PQ...
можно чуточку поподробнее
 
 
 
#6
22.01.2018 16:36:06
Кнопка "слияние".
Я ж вам готовый пример сделал и Андрей VG,  его модифицировал даже слегка
Вот горшок пустой, он предмет простой...
 
 
 
#7
22.01.2018 16:40:41
я пример вижу, но не копировать же его через расширенный редактор, хочу понять как это "кнопками" делается
у меня пока на шаге Сгруппированные строки есть уникальная номенклатура и дата (макс) - у вас уже таблица...
слияние - это объединение запросов?
 
 
 
#8
22.01.2018 16:54:48
Слияние это объединение таблиц. А дадите вы этой функции объединять запросы или шаги внутри одного запроса или на ходу сгенерированные таблицы - это без разницы.
Тот пример, что я сделал описан во второй части моего поста. То, что я описал только кнопками можно сделать если делать 2 запроса, в противном случае все равно придется ручками лезть.
Во вложении вариант, сделанный только путем кнопочного хардкора! Смотрите рыженькую табличку.
Изменено: PooHkrd - 22.01.2018 16:55:52
Вот горшок пустой, он предмет простой...
 
 
 
#9
22.01.2018 17:09:24
я правильно понимаю, что если у мен нет исходной таблицы ...у меня это пока запрос, то чтобы получить на выходе таблицу в нужном формате мне нужно писать код?

вернее я понял логику Вашего последнего примера:
1. есть таблица с исходными данными (у меня это пока только запрос)
2. делаем 1 запрос для определения последней даты цены по номенклатуре
3. делаем 2 запрос и объединяем с 1
 
 
 
#10
22.01.2018 17:15:27
По поводу логики все именно так.
А на тему прям "писать код" это не обязательно, прямо в запросе делаете группировку для определения максимальной даты для каждой номенклатуры, после чего переименовываете столбец Номенклатура и делаете слияние по двум столбцам запроса с самим собой. По умолчанию создатся шаг объединяющий предыдущий шаг с самим собой. Вот в строке формул этого шага для первого аргумента нужно будет указать вместо предпоследнего шага тот, который был до группировки, и в качестве ключевых столбцов нужно будет вместо переименованного столбца указать название оригинального. После такого вот ручного вмешательства разворачивайте присоединенный столбец и получите искомое.
Изменено: PooHkrd - 22.01.2018 17:22:10
Вот горшок пустой, он предмет простой...
 
 
 
#11
22.01.2018 17:42:54
сколхозил из 2 запросов, не выходила потому что имена столбцов поменял...спасибо

можно поподробнее то что вы указали в последнем посте, эта конструкция должна быть "воздушнее" моего колхоза )
 
 
 
#12
22.01.2018 17:51:46
Смотрите запрос под названием "Воздушный_вариант", на шаге №4 я сделал слияние и появилась строка кода:
Код
Table.NestedJoin(#"Сгруппированные строки",{"Код номенклатуры", "Количество"},#"Сгруппированные строки",{"Код номенклатуры", "Количество"},"NewColumn",JoinKind.LeftOuter)

Я её переделал вот в такую:
Код
= Table.NestedJoin(#"Измененный тип",{"Код номенклатуры", "Дата"},#"Сгруппированные строки",{"Код номенклатуры", "Количество"},"NewColumn",JoinKind.LeftOuter)

Ну, а дальше, сами разберетесь. Желтая табличка.
Изменено: PooHkrd - 22.01.2018 17:52:27 (Забыл добавить файл)
Вот горшок пустой, он предмет простой...
 
 
 
#13
23.01.2018 14:41:09
PooHkrd, спасибо, решил воздушный вариант...без выгрузки на лист, только подключения

но при дальнейшем слиянии этого запроса (определяет последнюю цену где то 200шт) с другим запросом (определяет необходимую номенклатуру, порядка 5к строк) по номенклатуре выдает ошибку, что запрос ссылается на другие запросы или этапы и не может напрямую обращаться к источнику данных. Измените эту комбинацию данных.

Количество слияний ограничено или что?
 
 
 
#14
23.01.2018 14:58:54
Не знаю как вам помочь не видя всего запроса. Но подозреваю, что вы там где-то зациклились.
А ранее предложенные варианты, мой или Андрея работают? Там без слияний/джойнов обходится.
Изменено: PooHkrd - 23.01.2018 15:01:03
Вот горшок пустой, он предмет простой...
 
 
 
#15
23.01.2018 15:03:13
все варианты, кроме воздушного удалил...
попробовал в PP задать связи, тоже не хочет...
я тоже думаю, что где то циклическая ссылка, но разве не должно подсвечивать такие места?
 
 
 
#16
23.01.2018 15:06:23
Цитата
PooHkrd написал:
Не знаю как вам помочь не видя всего запроса.
не пойму как лучше показать пример
есть папка с 2 подпапками и 2 запроса: первый сводит данные из одной папки (номенклатура с назначением), второй ищет последние цены по номенклатуре.
хотел в рамках первого запроса добавить второй, чтобы к каждой строке подцепилась цена. оба запроса только связи

не соображу как одним файлом сбросить
 
 
 
#17
23.01.2018 15:41:10
какая то ерунда
2-а запроса слил в 3-й, все получилось, но как то странно
1 запрос 3972 строк
2 запрос 176 строк
3 запрос слияние 1 и 2 - 4100строк
Как такое может быть?
 
 
 
#18
23.01.2018 15:45:47
Тогда ясно. Так нельзя делать - получается что вы берете Запрос А, берете из него данные и генерите из них запрос Б, после чего эти результаты пытаетесь закинуть опять в запрос А, вот здесь и цикл.
Нужно сделать запрос В, который в качестве источника будет брать запрос А, и к нему уже тащите данные из запроса Б. Тогда получится.
Для примера предлагаю ознакомиться с отличной статьей Максима Зеленского, где он подробнейшим образом разбирает эти моменты.

Цитата
muxey написал:
3 запрос слияние 1 и 2 - 4100строк
Это значит, что в запросе №2 у вас есть повторяющиеся номенклатуры, т.е. в один из дней по одной номенклатуре было более одной продажи. Это нужно убрать либо группировкой с каким-либо агрегатором, либо через удаление дубликатов.
Изменено: PooHkrd - 23.01.2018 15:50:53
Вот горшок пустой, он предмет простой...
 
 
 
#19
23.01.2018 16:33:11
Цитата
PooHkrd написал:
получается что вы берете Запрос А, берете из него данные и генерите из них запрос Б, после чего эти результаты пытаетесь закинуть опять в запрос А, вот здесь и цикл.
Запрос А и Запрос Б не связаны - я пока еще не освоил в такой степени PQ (я просто вывожу в сводной список уникальных номенклатур по запросу А, ручками его вставляю в 1С как фильтр и получаю цены за определенный период по всей группе). этот файлик закидываю в папку 2 и там запрос 2 выбирает последние цены - наверное не правильно, но пока проще не нашел - я только учуся)
Цитата
PooHkrd написал:
Для примера предлагаю ознакомиться с отличной  статьей  Максима Зеленского, где он подробнейшим образом разбирает эти моменты.
прочитал, но осилил толко первую треть, до остального нужно расти )
Цитата
PooHkrd написал:
Это значит, что в запросе №2 у вас есть повторяющиеся номенклатуры, т.е. в один из дней по одной номенклатуре было более одной продажи. Это нужно убрать либо группировкой с каким-либо агрегатором, либо через удаление дубликатов.
истина! вроде бы проверил, но пропустил 1 значение. Такие случаи маловероятны, но встретились...
Применил удаление дубликатов - но не понял какой принцип удаления и настраивается ли он?
 
 
 
#20
23.01.2018 16:41:48
Принцип удаления простой - оставляется последняя найденная строка из всех эквивалентных.
Вот горшок пустой, он предмет простой...
 
 
 
Страницы: 1
Читают тему
Наверх