Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Поиск последней актуальной цены на дату продажи Power Query
 
#1
11.06.2021 14:37:45
Добрый день, уважаемые форумчане!

Необходимо в данные продаж подтянуть последнюю актуальную на дату продажи цену.
В случае, если нет данных о цене на даты до продажи, вывести самую раннюю установленную цену (независимо от даты этой цены). Если данных о цене нет вообще - null.

На маленьких объемах данных работает следующий код:
Код
    #"Объединенные запросы" = Table.NestedJoin(#"Измененный тип",{"Товар"},#"Цены",{"Товар"},"Цена",JoinKind.LeftOuter),
    #"Замененное значение" = 
        Table.ReplaceValue(
            #"Объединенные запросы",
            each [#"Цена"],
            each if Table.IsEmpty( [#"Цена"] ) then null else
                let
                    OrderDate = [Дата],
                    MinPriceDate = List.Min([#"Цена"][Дата]),
                    FilterDate = List.Max({OrderDate,MinPriceDate}), 
                    Filter = Table.LastN(Table.SelectRows([#"Цена"], each [Дата] <= FilterDate ), 1)[Цена]{0}
                in Filter,
            Replacer.ReplaceValue,
            {"Цена"}
        )

Но на больших объемах безумно медленно.

Может, кто-то подскажет более быстрый код.
Пример.xlsx (19.37 КБ)  
 
 
 
#2
11.06.2021 15:32:14
Попробуйте такой вариант на вашем массиве.
Код
let
 price = Table.TransformColumnTypes(
   Excel.CurrentWorkbook(){[Name = "Цена"]}[Content],
   {{"Дата", type date}}
 ),
 sales = Table.TransformColumnTypes(
   Excel.CurrentWorkbook(){[Name = "Продажи"]}[Content],
   {{"Дата", type date}}
 ),
 order = Table.AddColumn(sales, "Порядок", each 1) & price,
 sort = Table.Sort(order, {{"Товар", Order.Ascending}}),
 grouped = Table.Group(
   sort,
   {"Товар"},
   {
     {
       "tab",
       each Table.FillUp(
         Table.FillDown(
           Table.Sort(_, {{"Дата", Order.Ascending}, {"Порядок", Order.Descending}}),
           {"Цена"}
         ),
         {"Цена"}
       )
     }
   },
   0
 ),
 combine = Table.Combine(grouped[tab]),
 filtered = Table.SelectRows(combine, each ([Порядок] = 1)),
 remove = Table.RemoveColumns(filtered, {"Порядок"})
in
 remove
Изменено: vikttur - 11.06.2021 19:08:22
Вот горшок пустой, он предмет простой...
 
 
 
#3
11.06.2021 17:21:05
PooHkrd, работает гораздо быстрее моего кода из примера, Благодарю.
 
 
 
#4
11.06.2021 21:41:28
PooHkrd, все равно ещё хочу оптимизировать;)
Товар ~ 1000 строк
Факт ~ 700 000 строк

Выполнение Вашего кода ~3,5 мин

Если взять декартово произведение товара и дат за 3 года, обьединить с ценой, заполнить вниз, заполнить вверх и обьединить с таблицей фактов по дате и товару - выполнение ~2,5-3 мин. Но есть проблема наличия нескольких цен на 1 дату. Группировка таблицы цен по дате и выводом более поздней по времени цены приводит к 5-6 мин выполнению.
Не великие же объемы, почему же так долго?;)

Попробую Ваш код применить к сгруппированной по дате и товару таблице фактов (объединю разных клиентов, примерно в 30 раз меньше строк) и дальнейшему объединению с полной таблицей фактов по дате и номенклатуре.
Изменено: surkenny - 11.06.2021 21:47:42
 
 
 
#5
14.06.2021 20:11:05
PooHkrd, в общем результаты:

Ваш изначальный код - 3:25
expand вместо combine - 2:05
фильтр по порядку внутри группировки и expand вместо combine - 1:45 (остаётся товар из таблицы цен без данных в таблице фактов, но его фильтрация далее не занимает время)

Благодарю за помощь!
 
 
 
#6
24.03.2022 13:52:57
Есть ли похожий алгоритм для power bi?
 
 
 
#7
24.03.2022 14:02:27
Marina Balikina, я бы на Вашем месте сначала поизучал PBI хоть немного. В PBI тоже используется PQ для обработки данных. Почему бы не использовать именно этот алгоритм, а не похожий? :)
 
 
 
#8
24.03.2022 14:05:46
Можете рекомендовать формулу pq для данной задачи?
 
 
 
#9
24.03.2022 14:44:51
Marina Balikina, не понял :) В ответе #2 же все есть.
 
 
 
#10
28.03.2024 19:05:04
Вечер добрый Уважаемые форумчане.
Помогите тупому человеку. Куда нужно вставить код макроса чтобы все это заработало

Не могу прикрепить образец из за большого объема данных

Все нашел. Все работает.
Изменено: Komil_8888 - 30.03.2024 14:20:54
 
 
 
Страницы: 1
Наверх