Как в PowerQwery обратиться к предыдущей строке
Предыдущая строка в PowerQwery.xlsx (50,3 KiB, 217 скачиваний)
С виду задача достаточно тривиальная и не сложная с точки зрения работы в Excel: сравнить значение ячейки столбца с ячейкой того же столбца, но в строке выше. Возьмем простую таблицу курсов валют:
Нужен еще один столбец, в котором в зависимости от сравнения текущей ячейки с предыдущей будет выводить текст Повышение или Понижение, в зависимости от того, стал ли в текущей ячейке курс выше или ниже относительно предыдущей ячейки. В самом Excel такое сравнение делается простой формулой:
Полная формула при этом выглядит так(вписываем в ячейку C3):
Просто, легко и понятно.
Но как быть, если те же самые действия надо проделать прямо в запросе PowerQwery? К сожалению там нет прямого механизма ссылок на строки. Мы можем обратить к конкретной строке или ячейке в ней таким образом:
Где
Здесь основная проблема в том, что в PowerQwery нет функции, которая бы возвращала для каждой строки её номер. Что-то вроде CurrentRow(ТекущаяСтрока). Но там есть возможность добавления столбца Индекса: Добавить столбец
Теперь у нас по факту есть индексация строк, которую мы сможем использовать. Чтобы обратиться к предыдущей строке нам необходимо будет взять имя источника из последнего шага запроса (для лучшего понимания я буду использовать обращение Источник) и добавить к нему в фигурных скобках ссылку на индекс строки -1, а в квадратных – имя столбца, значение из которого надо получить:
Теперь применяем это к нашей ситуации - добавляем новый столбец (Добавить столбец
if [Курс]>Источник{[Индекс]-1}[Курс] then "Повышение" Else "Понижение" |
По сути это и есть формула ЕСЛИ: если значение текущей строки курса больше значения предыдущей строки, то возвращаем «Повышение», иначе – «Понижение». Условие уже обрабатывается, за исключением двух но:
- Помимо понижения есть еще и равенство. Значит надо добавить еще одну проверку:
if [Курс]<Источник{[Индекс]-1}[Курс] then "Понижение" Else null
Если текущая ячейка меньше вышестоящей, то пишем «Понижение», иначе записываем значение
null (это аналог пустой ячейки в Excel в PowerQwery) - В таком виде на первой строке таблицы наш запрос неизбежно выдаст ошибку «Индекс не может быть отрицательным». И хотя ошибка возникнет только на одно первой строке – это все равно не очень приятно, т.к. в итоге запрос может просто не загрузиться(хотя в последних версиях это на загрузку не влияет). Чтобы этого избежать просто добавляем еще одно условие «поверх» уже созданных – проверка на то, что текущий индекс больше 0:
if [Индекс] > 0 then
В итоге наша "формула" будет выглядеть так:
if [Индекс] > 0 then if [Курс]>Источник{[Индекс]-1}[Курс] then "Повышение" else if [Курс]<Источник{[Индекс]-1}[Курс] then "Понижение" else null else null |
Все, можно нажимать Ок. Будет добавлен столбец, в котором «формула» обработает все строки. Останется только удалить столбец индекса, т.к. он нам больше не нужен и выгрузить данные на лист: Главная
Предыдущая строка в PowerQwery.xlsx (50,3 KiB, 217 скачиваний)
Статья помогла? Поделись ссылкой с друзьями!
