Данная статья относится к надстройке Power Query в Excel 2010/2013, к группе Скачать и преобразовать вкладки Данные в Excel 2016, и к экрану Get Data в Power BI Desktop. Термин «Power Query» используется в том же контексте, что и в предыдущих статьях.

Иногда, при работе с данными таблиц Power Query возникает необходимость получить значение из одной ячейки таблицы. В статье показано, как это сделать через Редактор запросов и Редактор кода. Также подробно обсуждаются дополнительные возможности, доступные в последнем. Кстати, эта тема частично раскрыта в главе М книги Power Query за авторством Криса Вебба, но к настоящему моменту она несколько устарела.

Предположим, что источник данных – таблица Excel, подобная такой:

Импортируем её в Power Query. Чтобы получить данные из ячейки второго столбца второй строки щелкаем по ней ПКМ и выбираем пункт Детализация углублением:

Готово, мы получили 5 в ответе:

Обратите внимание, что это значение 5 не то же самое, что и в ячейке таблицы. Запрос Power Query может вернуть любой тип данных. В данном случае будет возвращено значение целочисленного типа, а не значение типа таблица. Если вывести результаты этого запроса на лист Excel, то мы увидим отформатированную таблицу. Но если использовать результаты этого запроса в качестве входных данных для другого (например, как фильтр в SQL-запросе), то иметь данные целочисленного типа удобнее, чем таблицу из одной строки и столбца.

Вот код для действий на скриншотах выше. Вероятно, вы догадались как он работает.

Рассмотрим эти три шага: – Источник – получаем данные из таблицы Excel
– "Измененный тип" – устанавливаем тип данных для трех столбцов в целочисленный
– poleB – возвращает значение ячейки из второй строки столбца В (строки начинаются с 0).

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

"Измененный тип"{1}[poleB]

вернёт значение ячейки из второй строки столбца poleB., т.е 5. Аналогично, выражение

"Измененный тип"{0}[poleC]

вернёт значение 3, соответствующее первой строке столбца poleC.

Отметим, что ссылки на столбец и строку могут идти в любом порядке, и выражение #"Измененный тип"{1}[poleB] вернёт то же самое, что и

"Измененный тип"[poleB]{1}

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

Что произойдёт если использовать ссылку на отсутствующий столбец и/или строку? Конечно, мы получим сообщение об ошибке. Вернёмся к нашему примеру и запишем:

"Измененный тип"{4)[poleB]

и

"Измененный тип"{1)[poleD]

Оба выражения вернут ошибку, т.к. в таблице нет 5-ой строки и столбца poleD.

Однако вместо ошибки можно получить значение NULL, используя оператор "?" после ссылки. Например, выражение

"Измененный тип"{1}[poleD]?

вернёт null вместо сообщения об ошибке:

Но будьте осторожны! Выражение

"Измененный тип"{4}?[poleB]

по-прежнему возвращает ошибку, но не потому что отсутствует пятая строка, а потому что ссылка на пятую строку вернёт null, а у него нет столбца poleB.

Решением может быть изменение порядка ссылок:

"Измененный тип"[poleB]{4}?

или применение оператора "?" для обеих ссылок:

"Измененный тип"{4}?[poleB]?

К сожалению, применение оператора "?" не позволит избежать ошибок, если использовать отрицательные значения в ссылках строк.

Знаете ли вы, что таблицы Power Query могут содержать первичный ключ (т. е. один или несколько столбцов, значения которых уникально идентифицируют каждую строку), определяемый самой надстройкой? Нет? Неудивительно, это вовсе не очевидно из пользовательского интерфейса. Однако, существует несколько ситуаций, когда Power Query определяет первичный ключ для таблицы, в том числе:

1. Когда импортируются данные из таблицы реляционной базы данных, подобной SQL Server, и таблица уже имеет первичный ключ.
2. Когда используется кнопка Удалить повторения чтобы убрать повторяющиеся значения из столбца или столбцов, скрытно вызывается функция Table.Distinct()
3. Когда к таблице применяется функция Table.AddKey()

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

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

Если вы загрузите таблицу в Power Query, щелкните ПКМ по заголовку столбца poleKey и выберете пункт Удалить повторения, то установите этот столбец первичным ключом.

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

Убрав дубликаты, повторим действия с пунктом Детализация углублением. Получим следующее:

Обратите внимание на последний шаг, это важно! Вместо ссылки по номеру строки идёт ссылка по первичному ключу.

"Удаленные дубликаты"{[poleKey="Строка 2"]}[poleB]

Можно продолжать использовать нотацию на основе номера строки, но если таблица имеет столбец с первичными ключами, то можно использовать нотацию с первичным ключом.

Возможность ссылок на отдельные значения невероятно полезна в определенных типах запросов и расчётов. Однако стоит помнить, что зачастую существует несколько способов решения задачи, и не все они одинаково хороши.
Одно очевидное применение техники описанной в статье – запись предыдущих вычислений там, где необходимы ссылки на значения предыдущей строки таблицы. Но по опыту известно, что запись расчетов, использующих ссылки на строку/столбец не даёт осуществлять Query Folding («квэри фолдинг» — термин, означающий передачу тяжелых операций по обработке запросов на сторону сервера при работе с совместимой базой данных, на текущий момент это MS SQl, прим. пер.), и ведет к снижению производительности.
Возможно, альтернативные подходы (некоторые описаны в статьях Implementing Common Calculations In Power Query и Join Conditions in Power Query, Part 2: Events-In-Progress, Performance and Query Folding) будут лучшим выходом.

Нет каких-то общих правил, которые можно посоветовать, вы должны сами попробовать разные способы.