PowerQuery Расчет вариант расчета ввоза товара на склад по выполнению условия остатка, Получить промежуточный результат внутри вычисляемого столбца с учетом вычислений более ранних строк этого же столбца
Здравствуйте. Помогите пожалуйста рассчитать 2 колонки в PowerQuery, и если, возможно, помогите оптимизировать код, который был написан ранее. Вцелом все колонки кроме последних 2х рассчитал. Не получается посчитать именно ввоз и остаток, не получается сделать выключатель ввоза, аналогично тому, как это реализовано на формулах. Также, пожалуйста, если возможно, подскажите как оптимизировать алгоритм расчета накопительных итогов, при больших объемах данных все очень сильно тормозит, и это ОЧЕНЬ мягко говоря.
Если перефразировать, то проблема у меня в том, чтобы ссылаться на вычисление своего собственного столбца на шаг ранее, как это реализовано на формулах в стандартном виде рассчета. Не пойму как это реализовать, упираюсь в циклическую ссылку. Не пойму как взять сумму вычисляемого столбца, на строчку выше, в конкретном случае, если добавить столбец ввоз, то на сам же столбец, на все строки кроме последней.
Доброе время суток. Вот с оконными функциями в Power Query - беда. Можно конечно, но лучше в БД их поддерживающую, например в SQLite, а уже оттуда загружать куда надо через Power Query.
Андрей VG, Задача в указанном контексте определена как нерешаемая???????Никаким хитрым ListGenerate ни какой вложенной функцией не решается????? Что же делать??? Глупо ведь расчеты через промежуточные данные пускать. Мне нужно бюджет свернуть, все денные считаются в PQ, ожидаемые продажи, валовой доход. Остатки ТМЦ на дату. Нет никакого решения совсем? PQ ведь не поддерживает без хитрой настроки R никакую выгрузку в БД вообще. Решения нет????
buchlotnik, Я просто в панике! Очень многое уже сделано в PQ. Простите, Вы не могли бы пояснить алгоритм? Что происходит в Generate? Не затруднит ли Вас прочитать цикл по русски?
//просто поток сознания, накакой оптимизации
l1 = ОбщПродажи[ВвозСумм], //список ввозимых сумм
l2 = ОбщПродажи[ОбщПродажи], //список продаж
n = List.Count(l1), //число строк
g = List.Generate(//начинаем генерацию
()=>[i=0,//счётчик в нуле
s=l1{0},//вспомогательная сумма равна первому ввозу
g=l1{0}],//целевое значение равно первому ввозу
each [i]<n,//пока счётчик меньше n
each [i=[i]+1,//счётчик увеличили на единицу
s=if l2{[i]+1}>[s] then [s]+l1{[i]+1} else [s],//проверили - если общие продажи превысили вспомогательную сумму - увеличиваем её на очередной ввоз, иначе сохраняем
g=if l2{[i]+1}>[s] then l1{[i]+1} else 0],//и в целевом столбце также прописали сумму ввоза, иначе ноль
each [g]),//выводим только список целевых значений
to = Table.FromColumns(Table.ToColumns(ОбщПродажи)&{g},Table.ColumnNames(ОбщПродажи)&{"Решения нет"})
//поток окончен
Соблюдение правил форума не освобождает от модераторского произвола
а вообще Пользовательский1 СуммыВвоз и ОбщПродажи также нужно переписать все в один List.Generate - чтобы за одну проходку сделать все оконные вычисления - это будет гораздо быстрее
buchlotnik, Я понимаю конечно, что это должно быть в разы быстрее, но как это сделать? Суммы считаются в отдельном запросе, на каждый месяц они различные. И это выводится не алгоритмами, а ручным вводом. Т.е. грубо менеджер сказал что в июне продажи будут 124 штуки а в июле 55, и так далее. Связи нет. Поэтому я и беру данные фильтрами. Все это несколько усугубляется тем, что я привел одну таблицу с условным наименованием, а таких наименований порядка 50ти. Алгоритм нужно немного допилить, завтра подумаю как. Если задать значение S изначально преувеличенным, например 5000, все равно первый проход он выставляет. Т.е. даже если на остаток есть в наличии, все равно первые 620 штук говорит что нужно ввезти.
у вас весь пример рассчитан от одного столбца - Продажи, так что собственно мешает делать так:
Скрытый текст
Код
let
from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content][Продажи],
nonull = List.ReplaceValue(from,null,0,Replacer.ReplaceValue),
k = Дни{0}[Знч],
l = Дни{1}[Знч],
m = Дни{2}[Знч],
n =List.Count(nonull),
lst1 = nonull&List.Repeat({0},List.Max({k,l})),
gen = List.Generate(
()=>[i=0,
b = lst1{0},
o=List.Sum(List.FirstN(nonull,k)),
v=List.Sum(List.FirstN(nonull,l)),
s= b,
ss=v,
g=v,
e=ss+m-s],
each [i]<n,
each [i=[i]+1,
b = lst1{[i]+1},
o=[o]+lst1{[i]+k}-lst1{[i]},
v=[v]+lst1{[i]+l}-lst1{[i]},
s=[s]+b,
ss=if s>[ss] then [ss]+v else [ss],
g=if s>[ss] then v else 0,
e=ss+m-s],
each [[b],[o],[v],[s],[g],[e]]),
to = Table.FromRecords(gen)
in
to
ну и оцените скорость на рабочих данных - просто быстрее вряд ли выйдет, а на 50k этот вариант уже изрядно затупил
let
//взяли исходные данные из таблицы Дни
k = Дни{0}[Знч],
l = Дни{1}[Знч],
m = Дни{2}[Знч],
// в исходном списке продаж заменили пропуски на нули и добавляем нулевой хвост, чтобы не было ошибки на последних строках
from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content][Продажи],
nonull = List.ReplaceValue(from,null,0,Replacer.ReplaceValue),
lst1 = nonull&List.Repeat({0},List.Max({k,l})),
n =List.Count(nonull),//счётчик - количество текущих данных
//генерим
gen = List.Generate(
//область исходных значений
()=>[i=0,//счетчик в нуле
b = lst1{i},//столбец продаж - первая продажа
o=List.Sum(List.FirstN(nonull,k)),//столбец остатков - сумма первых k
v=List.Sum(List.FirstN(nonull,l)),//столбец ввозов - сумма первых l
s= b,//общая сумма равна первой продаже
ss=if m-s<o then v else 0,//вспомогательная сумма ss - в ней будем суммировать ввозы, если остаток ниже минимального
g=if m-s<o then v else 0,//столбец самих ввозов
e=g+m-s],//последний вычисляемый - просто по формуле
//проверка условия i меньше предельного
each [i]<n,
//преобразования на текущем шаге
each [i=[i]+1,//счётчик +1
b = lst1{i},//продажа по номеру
o=[o]+lst1{[i]+k}-lst1{[i]},//чтобы пересчитать скользящее окно плюсуем следуюую продажу, отстоящую от текущей на k и вычитаем предыдущую
v=[v]+lst1{[i]+l}-lst1{[i]},//аналогично для ввозов
s=[s]+b,//к общей сумме добавляем очередную продажу
ss=if m-s+[ss]<o then [ss]+v else [ss],//проверка неснижаемости остатка !важная разница s - сумма ТЕКУЩЕЙ итерации, [ss] - сумма с ПРЕДЫДУЩЕЙ итерации
g=if m-s+[ss]<o then v else 0,//аналогичная проверка для самого ввоза - пишем ввоз или ноль
e=ss+m-s],//последний столбец по той же формуле
//показываем какие поля нам нужны в итоге
each [[b],[o],[v],[s],[g],[e]]),
to = Table.FromRecords(gen) //превращаем полученный список в таблицу
in
to