Как в PowerQwery обратиться к предыдущей строке

Скачать файл с исходными данными, используемый в видеоуроке:

  Предыдущая строка в PowerQwery.xlsx (50,3 КиБ, 1 216 скачиваний)

С виду задача достаточно тривиальная и не сложная с точки зрения работы в Excel: сравнить значение ячейки столбца с ячейкой того же столбца, но в строке выше. Возьмем простую таблицу курсов валют:

Нужен еще один столбец, в котором в зависимости от сравнения текущей ячейки с предыдущей будет выводить текст Повышение или Понижение, в зависимости от того, стал ли в текущей ячейке курс выше или ниже относительно предыдущей ячейки. В самом Excel такое сравнение делается простой формулой: =B3>B2
Полная формула при этом выглядит так(вписываем в ячейку C3):
=ЕСЛИ(B3>B2;"Повышение";ЕСЛИ(B3<B2;"Понижение";""))
Просто, легко и понятно.
Но как быть, если те же самые действия надо проделать прямо в запросе PowerQwery? К сожалению там нет прямого механизма ссылок на строки. Мы можем обратиться к конкретной строке или ячейке в ней таким образом:
Источник{Номер_строки}[Имя_столбца]
Где Источник – это загруженная в запрос таблица(первый шаг любого запроса, по сути. В англ.версии это будет Source). Номер строки указывается в фигурных скобках(отчет идет с 0), а имя столбца – в квадратных. Т.е. если хотим обратиться к 3-ей строке столбца Курс, то записать можно так: Источник{2}[Курс]
Здесь основная проблема в том, что в PowerQwery нет функции, которая бы возвращала для каждой строки её номер. Что-то вроде CurrentRow(ТекущаяСтрока). Но там есть возможность добавления столбца Индекса: Добавить столбец(Add Column) -Столбец индекса(Index Column). Им мы и воспользуемся. Добавляем такой столбец (лучше брать с 0, т.к. в PowerQwery нумерация строк так же начинается с нуля) и назовем его Индекс(если такое название не было дано автоматически):

Теперь у нас по факту есть индексация строк, которую мы сможем использовать. Чтобы обратиться к предыдущей строке нам необходимо будет взять имя источника из последнего шага запроса (для лучшего понимания я буду использовать обращение Источник) и добавить к нему в фигурных скобках ссылку на индекс строки -1, а в квадратных – имя столбца, значение из которого надо получить:
Источник{[Индекс]-1}[Курс]
Теперь применяем это к нашей ситуации - добавляем новый столбец (Добавить столбец(Add Column) -Настраиваемый столбец(Custom Column)) и прописываем в нем конструкцию:

if [Курс]>Источник{[Индекс]-1}[Курс] then
   "Повышение"
Else "Понижение"

По сути это и есть формула ЕСЛИ: если значение текущей строки курса больше значения предыдущей строки, то возвращаем «Повышение», иначе – «Понижение». Условие уже обрабатывается, за исключением двух но:

1. Помимо понижения есть еще и равенство. Значит надо добавить еще одну проверку:

   if [Курс]<Источник{[Индекс]-1}[Курс] then "Понижение" Else null

   if [Курс]<Источник{[Индекс]-1}[Курс] then "Понижение" Else null

   Если текущая ячейка меньше вышестоящей, то пишем «Понижение», иначе записываем значение null(это аналог пустой ячейки в Excel в PowerQwery)

2. В таком виде на первой строке таблицы наш запрос неизбежно выдаст ошибку «Индекс не может быть отрицательным». И хотя ошибка возникнет только на одно первой строке – это все равно не очень приятно, т.к. в итоге запрос может просто не загрузиться(хотя в последних версиях это на загрузку не влияет). Чтобы этого избежать просто добавляем еще одно условие «поверх» уже созданных – проверка на то, что текущий индекс больше 0:

   if [Индекс] > 0 then

   if [Индекс] > 0 then

В итоге наша "формула" будет выглядеть так:

if [Индекс] > 0 then
    if [Курс]>Источник{[Индекс]-1}[Курс] then 
        "Повышение"
    else
        if [Курс]<Источник{[Индекс]-1}[Курс] then 
            "Понижение"
        else null
else null

Все, можно нажимать Ок. Будет добавлен столбец, в котором «формула» обработает все строки. Останется только удалить столбец индекса, т.к. он нам больше не нужен и выгрузить данные на лист: Главная(Home) –Закрыть и загрузить(Close & Load).

Скачать файл с готовым запросом:

  Предыдущая строка в PowerQwery.xlsx (50,3 КиБ, 1 216 скачиваний)

Одна проблема – такой подход как описан выше ОЧЕНЬ тяжелый. Т.е. на более-менее больших таблицах тормозить будет основательно. Поэтому имеет смысл применить иной подход, который на первый взгляд кажется более тяжелым начинающим работать с Power Query. Основан он все на том же столбце индекса, но мы просто создадим таких столбца два: в одном индекс с 0, а в другом – с 1.
Итак, у нас есть все та же исходная таблица, которую мы загрузили в Power Query. Добавляем столбец Индекса с 0: Добавить столбец(Add Column) -Столбец индекса(Index Column) -от 0:

Далее делаем точно тоже самое - добавляем еще один столбец индекса, но индекс указываем – от 1.
Теперь переходим на вкладку Главная(Home) -Объединить(Merge) -Объединить запросы(Merge queries):

В появившемся окне для верхней части выделяем столбец Индекс, в нижней выбираем эту же таблицу(она помечается как «текущее») и выделяем столбец Индекс.1. В качестве Типа соединения выбираем «Внешнее соединение слева (все из первой таблицы, совпадающие из второй)»:

Раскрываем полученный столбец нажатием на разнонаправленные стрелки и выбираем там только один столбец «Курс» и снимаем галочку с пункта «Использовать имя столбца как префикс(Default column name prefix)»:

В итоге мы получим еще один столбец, в котором будет курс валют, но со смещением на одну строку относительно каждой строки исходной таблицы:

Далее необходимо убедиться, что первая строка(где Индекс равен 0) не переместилась в конец таблицы. Если переместилась – то сортируем столбец с датами по возрастанию: жмем на стрелочку в правой части столбца Дата -Сортировка по возрастанию(Sort Ascending)). Если в реальной таблице нет дат - то сортируем по столбцу Индекс, т.к. он отражает как раз первоначальный порядок строк в таблице.
Все, что нам останется – это корректно сравнить два столбца. Добавляем новый столбец (Добавить столбец(Add Column) -Настраиваемый столбец(Custom Column)) и прописываем формулу:

if [Курс]>[Курс.1] then "Повышение" else 
    if [Курс]<[Курс.1] then "Понижение" else "Равно"

Этого вполне достаточно, но на первой строке мы получим ошибку сравнения, т.к. для столбца «Курс.1» у нас значение будет "null", которое нельзя использовать для сравнения. В принципе, можно просто убрать ошибки, т.к. эту строку все равно не с чем сравнивать(в столбец же null). Правая кнопка мыши по столбцу с нашей функцией -Удалить ошибки(Remove errors):

Будут удалены все строки с ошибочными вычислениями. В нашем случае это одна не очень значащая строка. Но можно и в том же вычислении просто дописать еще одно условие:

if [Курс.1] = null then null else 
    if [Курс]>[Курс.1] then "Повышение" else 
        if [Курс]<[Курс.1] then "Понижение" else "Равно"

тогда строка не будет удалена, а вернет значение null.
Теперь можно удалить лишние столбцы индекса и курса и все готово.

На первый взгляд такой метод кажется дольше и муторнее, но в итоге он отработает намного быстрее первого метода.
Скачать файл с готовым запросом:

  Tips_PBI_PreviouseRow_2.xlsx (50,9 КиБ, 259 скачиваний)