Страницы: 1
RSS
Поиск последней актуальной цены на дату продажи Power Query
 
Добрый день, уважаемые форумчане!

Необходимо в данные продаж подтянуть последнюю актуальную на дату продажи цену.
В случае, если нет данных о цене на даты до продажи, вывести самую раннюю установленную цену (независимо от даты этой цены). Если данных о цене нет вообще - 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 КБ)  
 
Попробуйте такой вариант на вашем массиве.
Код
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
Вот горшок пустой, он предмет простой...
 
PooHkrd, работает гораздо быстрее моего кода из примера, Благодарю.
 
PooHkrd, все равно ещё хочу оптимизировать;)
Товар ~ 1000 строк
Факт ~ 700 000 строк

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

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

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

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

Благодарю за помощь!
Страницы: 1
Читают тему (гостей: 1)
Наверх