Lost your password?


Хитрости »
Основные понятия (27)
Сводные таблицы и анализ данных (10)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (23)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (68)
Разное (43)
Баги и глюки Excel (5)

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


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

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


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

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

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

  1. Помимо понижения есть еще и равенство. Значит надо добавить еще одну проверку:
    if [Курс]<Источник{[Индекс]-1}[Курс] then
       "Понижение"
    Else null

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

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

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

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

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

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

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


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



Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистика
Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2024 Excel для всех   Войти