Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Power Query. Подстановка цены на товар в зависимости от даты.
 
#1
17.06.2021 17:06:09
Добрый день, форумчане.

Есть две таблицы: Остатки по товару с разбивкой по дате и Прайс на эти товары. С течением времени цена на товары меняется.
Помогите сделать результирующую таблицу, где указана действующая цена на конкретную дату.
 
 
 
#2
17.06.2021 18:14:51
Наверное, должно помочь: Поиск последней актуальной цены на дату продажи Power Query
 
 
 
#3
17.06.2021 18:27:32
Пользуйтесь)
 
 
 
#4
17.06.2021 19:45:21
DemonAMT, в случае непрерывности дат в таблице, куда нужно добавить цену, решение простое. Сложности, когда в таблице "фактов" может не быть даты из таблицы цен.
Ваш код можно  делать чуть лаконичнее (не нужно объединять дату и товар, join можно делать по нескольким столбцам:
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="tblTovar"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Дата", type date}, {"Остаток", Int64.Type}}),
    #"Объединенные запросы" = Table.NestedJoin(#"Измененный тип", {"Дата", "Товар"}, Price, {"Дата", "Товар"}, "Price", JoinKind.LeftOuter),
    #"Развернутый элемент Price" = Table.ExpandTableColumn(#"Объединенные запросы", "Price", {"Цена"}, {"Цена"}),
    #"Заполнение вниз" = Table.FillDown(#"Развернутый элемент Price",{"Цена"}),
    #"Заполнено вверх" = Table.FillUp(#"Заполнение вниз",{"Цена"})
in
    #"Заполнено вверх"
 
 
 
#5
18.06.2021 10:27:10
Спасибо за помощь, но оба варианта пока имеют нюансы.
В случае с решением:
Цитата
surkenny написал:
Наверное, должно помочь:  Поиск последней актуальной цены на дату продажи Power Query
Цену меняет на следующую дату. Например цена в таблице Цен изменилась 10.06, а в таблицу фактов она подставляется с 11.06. При разрыве дат обрабатывает этот разрыв корректно.

В случае со  вторым решением от DemonAMT и правками от surkenny разрывы дат обрабатывает некорректно, о чем и писал surkenny, но зато цены подставляет в нужные даты.
 
 
 
#6
18.06.2021 12:23:12
Belosludtsev, сортировку по порядку нужно сделать другой. Заменил на более быстрый на моих данных вариант (expand вместо combine и строки с ценами "убираем" внутри группировки).
Код
let
 price = Table.TransformColumnTypes(
   Excel.CurrentWorkbook(){[Name = "tblPrice"]}[Content],
   {{"Дата", type date}}
 ),
 sales = Table.TransformColumnTypes(
   Excel.CurrentWorkbook(){[Name = "tblTovar"]}[Content],
   {{"Дата", type date}}
 ),
 order = Table.AddColumn(sales, "Порядок", each 1) & price,
 sort = Table.Sort(order, {{"Товар", Order.Ascending}}),
 grouped = Table.Group(
    sort,
    {"Товар"},
    {
        {
        "tab",
        each Table.SelectRows(
            Table.FillUp(
                Table.FillDown(
                    Table.Sort(_, {{"Дата", Order.Ascending}, {"Порядок", Order.Ascending}}),
                    {"Цена"}
                ),
                {"Цена"}
            ),
            each [Порядок] = 1
        ),
        type table [Дата=date, Остаток=Int64.Type, Цена=number]
        }
    }
 ),
    expand = Table.ExpandTableColumn(grouped, "tab", {"Дата", "Остаток", "Цена"}, {"Дата", "Остаток", "Цена"}),
    reorder = Table.ReorderColumns(expand,{"Дата", "Товар"})
in
    reorder
 
 
 
#7
18.06.2021 12:38:55
surkenny, большое спасибо. Этот вариант самый быстрый из трех.
Если даты идут без пропусков, то можно так же в подобных задачах использовать решение от DemonAMT
Изменено: Belosludtsev - 20.05.2023 08:21:33
 
 
 
Страницы: 1
Наверх