Абсолютные и относительные ссылки в Power Query — подход в стиле Excel

Эта статья — перевод моего первого поста в этом блоге, который был опубликован 5 ноября 2015 года на английском языке. К моему удивлению, этот пост — самый популярный, за это время он набрал почти 21000 просмотров. С небольшими стилистическими правками публикую его на русском языке. В переводе помогал мой сын Дмитрий, за что ему отдельное спасибо.

Power Query — это мощный инструмент, способный на большее, чем просто брать данные из источника и переносить их в таблицу или Power Pivot. Данные можно очищать и преобразовывать множеством способов, но есть некоторые действия, привычные для Excel, которые не так удобно делать в Power Query.

Например, что, если мне нужна относительная ссылка на конкретную ячейку в таблице Power Query — значение из определённой строки в определённом столбце? Или ссылка на значение в определённом столбце на четыре строки выше? В Excel очевидно, как это сделать: нужно просто указать на нужное значение мышкой, убедиться, что из ссылки к строке убран знак «$» (знак абсолютной ссылки), и всё. Но в Power Query я не могу так просто это сделать.

Но всё же решение, хотя и непрямое, существует.

Прежде всего давайте выясним, как можно получить доступ к конкретному значению из таблицы Power Query.

Простейший способ понять адресацию в Power Query, по-моему, анализировать код шагов.

Абсолютные ссылки на строки

Допустим, у нас есть простая таблица из двух столбцов: даты (Date) и количества (Amount). В ней пять строк, и в первом столбце стоят, как ни странно, даты, во втором — какие-то значения:

 

Исходные данные

Мы хотим получить значение из ячейки B4, а именно 120.

В Excel это просто: мы просто пишем ссылку вроде =B4, или =R4C2 в стиле R1C1.

Можно ли использовать этот стиль ссылок (R1C1) в Power Query? Да.

Загрузите эту таблицу в Power Query, щёлкните правой мышкой по нужному значению и выберите “drill to details”. Отлично, мы получили 120.

Теперь взглянем на код.

let Source = Excel.CurrentWorkbook(){[Name="Tab_1"]}[Content], Amount = Source{2}[Amount] in Amount

 

1

2

3

4

5

let

    Source = Excel.CurrentWorkbook(){[Name="Tab_1"]}[Content],

    Amount = Source{2}[Amount]

in

    Amount

 

В третьей строке кода мы видим искомую ссылку. Заметьте, что мы ссылаемся на предыдущий шаг (как обычно), затем пишем {2} и затем название столбца [Amount].

Важно: строки и списки в Power Query нумеруются с нуля: первая позиция в списке всегда имеет номер 0, то же самое касается номеров строк в таблице. Поэтому, запрашивая в Power Query значение из третьей строки, мы должны использовать число 2 для обозначения строки.

Эта формула расшифровывается так: дай мне значение из поля [Amount] в строке с индексом 2 таблицы Source. Иными словами, формула ищет определённый номер записи в таблице и затем возвращает значение определённого поля из этой записи.

Формула, полученная нами из редактора, фактически использует два последовательных подхода: выражение:  Name{Argument}  и выражение  Record[Field].

Синтаксис  Name{Argument} имеет несколько реализаций:

если Name — это список (list), а  Argument— число (number), выражение возвращает элемент с соответствующим номером из списка Name;

если Name — это таблица (table), а Argument — число (number), выражение возвращает строку с соответствующим номером из таблицы Name;

если Name — это таблица (table), а Argument — запись (record), выражение возвращает уникальную строку из таблицы Name, в которой значения полей соответствуют значениям аналогичных полей записи Argument.

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

Выражение Record[Field] имеет почти такой же синтаксис: оно ищет поле в записи по имени поля. Если в записи нет указанного поля, код выдаёт ошибку.

Взгляните снова на нашу формулу: сперва мы получаем строку (запись) из таблицы Source с номером 2:

Source{2}

 

1

Source{2}

 

Затем из этой записи мы получаем поле [Amount] (строка таблицы — это запись с именами столбцов в качестве имён полей):

Source{2}[Amount]

 

1

Source{2}[Amount]

 

Выглядит просто, да?

Но погодите, если мы запросим в Power Query одиночный столбец из таблицы, мы получим как раз нужный список:

MyList = Source[Amount]

 

1

MyList = Source[Amount]

 

Так что, если нам нужно конкретное значение из этого списка (см. первый пункт в описании синтаксиса Name{Argument}выше), мы просто добавляем в фигурных скобках номер значения после имени списка:

MyValue = MyList{2}

 

1

MyValue = MyList{2}

 

Эти два шага можно сократить в один:

MyValue = Source[Amount]{2}

 

1

MyValue = Source[Amount]{2}

 

и получить искомое значение, 120.

Таким образом, у нас фактически есть два возможных способа обратиться к одиночному значению в таблице Power Query.

Давайте сравним два способа адресации:

Обратиться к определённой записи в таблице и получить определённое поле из этой записи: MyValue = Source{2}[Amount]

Получить список из столбца таблицы и получить значение по определённому номеру в этом списке: MyValue = Source[Amount]{2}

Они выглядят очень похоже, и может показаться, что нет разницы, в каком порядке ставить название столбца и порядковый номер. На самом деле это два разных подхода, и это нужно запомнить, чтобы использовать в будущем.

Но в любом случае оба подхода дают нам ключ к любым ссылкам на строки в таблицах Power Query.

Относительные ссылки на строки

В таблице-образце находятся данные, похожие на остатки на счёте. Предположим, нам нужно добавить столбец, вычисляющий и показывающий изменение значения на счёте. Для этого нужно получить значение из предыдущей строки и сравнить его со значением в текущей строке.

Но в предыдущих примерах мы видели, что ссылка на позицию и столбец зашиты внутрь формулы. Значит, нам нужно найти способ преобразовать номера строк в относительные ссылки.

И здесь нам (вновь) приходит на помощь Excel. Что бы мы сделали в Excel, чтобы произвести такое вычисление? В простом случае мы просто помещаем в ячейку C3 формулу  =B3-B2 и растягиваем её на столбец. Но, если мы запишем эту формулу в стиле R1C1, то получим следующее:  =RC[-1]-R[-1]C[-1]

Это значит: возьми значение из этой строки и столбца на один левее и вычти из него значение из строки на одну выше и столбца на один левее. В Power Query в большинстве случаев мы будем работать с данными из известных столбцов, так что давайте представим, что мы можем опустить часть, относящуюся к столбцу, и ссылаться только на строку, в стиле R1: =R–R[-1]

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

= Source{current_row_number}[Amount] - Source{current_row_number-1}[Amount]

 

1

= Source{current_row_number}[Amount] - Source{current_row_number-1}[Amount]

 

или

= Source[Amount]{current_row_number} - Source[Amount]{current_row_number-1}

 

1

= Source[Amount]{current_row_number} - Source[Amount]{current_row_number-1}

 

Итак, можно ли получить номер строки в Power Query? Да, с помощью специального столбца индексов Index.

Столбец индексов генерирует список целочисленных значений, начинающихся с N, с шагом S для каждой строки таблицы. Интерфейс Power Query позволяет выбрать один из двух наиболее популярных вариантов столбцов индексов: “From 0” (с нуля, по умолчанию) или “From 1” (с единицы, оба с шагом 1) — или создать собственный список со своими значениями параметров Start (начальное значение) и Step (шаг).

Давайте добавим столбец индексов, начинающихся с нуля, после шага Source в Power Query:

#"Added index" = Table.AddIndexColumn(Source, "Index", 0, 1)

 

1

#"Added index" = Table.AddIndexColumn(Source, "Index", 0, 1)

 

Теперь у нас есть всё, что нужно: мы знаем название столбца и можем получить из столбца индексов номер строки. Давайте переименуем предыдущий шаг в AddInd для ускорения написания и новым шагом добавим собственный вычисляемый столбец, вводя следующую формулу в диалоговое окно:

=AddInd[Amount]{[Index]}-AddInd[Amount]{[Index]-1}

 

1

=AddInd[Amount]{[Index]}-AddInd[Amount]{[Index]-1}

 

Таким образом, полная формула этого шага выглядит так:

= Table.AddColumn(AddInd, "Custom", each AddInd[Amount]{[Index]}-AddInd[Amount]{[Index]-1})

 

1

= Table.AddColumn(AddInd, "Custom", each AddInd[Amount]{[Index]}-AddInd[Amount]{[Index]-1})

 

Мы только что сделали следующее: в каждой строке таблицы мы ссылаемся на значение из столбца Index как аргумент для выражения Name{Argument}.

В этом примере я использовал тип ссылки по положению в списке (см. п.1), а не по записи в таблице. Посмотрите на результат в окне предпросмотра Power Query:

 

Здесь можно увидеть, как работают относительные ссылки на строки в Power Query

У нас есть искомый список изменений значений из столбца Amount, но с ошибкой (error) в первой строке. Очевидно, ведь строки с номером (0-1)=-1 нет. Если бы мы использовали ссылку больше последнего номера строки, то нужно было бы использовать “?” (вопросительный знак) после ссылки на номер строки. Это называется “факультативный выбор объекта”.

Давайте посмотрим, как это работает. Вернёмся на шаг назад и добавим ещё один столбец со следующей формулой:

=AddInd[Amount]{[Index]+1}?

 

1

=AddInd[Amount]{[Index]+1}?

 

Мы получим вот это:

 

«Факультативный выбор объекта» возвращает null, если выйти за границу количества строк

т.е. ссылка на несуществующий номер в списке была заменена значением null. Но, как я уже говорил, это работает только с номерами не меньше количества объектов в списке (или записей в таблице). Для отрицательных номеров формула выдаёт ошибку.

Теперь давайте уберём этот шаг и вернёмся к добавленному ранее столбцу с ошибкой в первой строке.

Что можно сделать с этой ошибкой? Мы можем добавить ещё один шаг с помощью UI: замену ошибок. Просто выбираем наш столбец Custom, затем на вкладке Transform выполняем Replace errors…, и вводим в диалоговое окно значение 0. Либо просто добавляем эту формулу в редактор:

RemErrs = Table.ReplaceErrorValues(AddCust1, {{"Custom", 0}})

 

1

RemErrs = Table.ReplaceErrorValues(AddCust1, {{"Custom", 0}})

 

Отлично, мы получили, что хотели:

 

Теперь мы заменили ошибки определёнными фиксированными значениями

Если мы хотим заменить ошибки не точными значениями, а другими вычислениями или функциями, лучше использовать конструкцию try - otherwise, работающую как функция IFERROR (ЕСЛИОШИБКА)в Excel. Нужно просто исправить предыдущий шаг (на котором мы добавляли первый вычисляемый столбец). Щёлкните шестерёнку (“gear”) рядом с именем шага и замените формулу следующей:

try AddInd[Amount]{[Index]}-AddInd[Amount]{[Index]-1} otherwise 0

 

1

try AddInd[Amount]{[Index]}-AddInd[Amount]{[Index]-1} otherwise 0

 

или внесите необходимые исправления прямо в строке формул. После оператора otherwise может идти любое выражение, например, вы можете использовать собственную функцию, ввести текст или вернуть null.

 

Теперь мы заменили ошибки с помощью выражения «try-otherwise»

Эй, да это же всё. Поздравляю, теперь мы знаем, как создавать абсолютные и относительные ссылки в таблицах Power Query.

Этот приём полезен во многих случаях, в основном при преобразовании и очистке сырых данных.

Но ОСТОРОЖНО: использование относительных ссылок в таблицах с большим количеством данных может поглощать очень много ресурсов и увеличивать время обновления запроса.

Вы можете загрузить книгу-образец, чтобы посмотреть, как это работает, и опробовать все приёмы из этой записи.

А как насчёт относительных ссылок по столбцу? Чуть сложнее и реже встречается, но мы можем сделать и это. В следующий раз.

Если вы хотите почитать больше о продвинутых преобразованиях и моделировании данных в Power Query, я рекомендую следующие книги: