Relative reference in calculated column

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

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

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

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

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

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

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

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

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

Absolute and Relative References in Power Query

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

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


Сравнение значений null в Power Query

Недавно мне нужно было сделать очень простую операцию в Power Query. В столбце с числами нужно было выполнить проверку «значение меньше N» и в новом столбце вывести соответствующий текст. Функция дополнительного столбца выглядит примерно так:

На самом деле некоторые значения — null (то есть пустые):

Данные содержат null и в результате сравнения возникает ошибка

И такая простая операция возвращает ошибку для этих значений!

Почему? Есть некоторая ловушка, спрятанная в глубинах документации (а именно на странице 67 PDF-файла  “Power Query Formula Language Specification (October 2016)”, который можно найти тут.

Continue Reading


Как получить несколько значений в результате запроса Power Query

Эта статья о работе надстройки Power Query к Excel 2010 и Excel 2013, редактора запросов Power BI и  группы команд “Get & Transform” (“Получить и преобразовать”) в Excel 2016. Надеюсь, когда-нибудь эта чехарда закончится и мы сможем говорить просто Power Query.

Результатом вычисления запроса в Power Query является единственное значение. Как правило, речь идет о таблице, которую мы затем выгружаем на лист или в модель данных в Excel и Power BI. Это же требование относится также и к любым другим выражениям, вычисляемым Power Query, например, встроенным или пользовательским функциям, полям записей, записям в целом, и т.д.

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

Промежуточные и дополнительные результаты запросов

Представьте, что в процессе сложных преобразований запроса Query1 последним шагом под названием ResultTable  мы получили нужный результат (таблицу), которую мы хотим загрузить в модель данных. Одним из промежуточных шагов в нашем запросе был расчет какой-то величины ValueX , и мы хотели бы использовать ее в других выражениях или запросах:

Нам в итоге нужен и ValueX , и, конечно же, ResultTable .

У нас есть как минимум три способа это сделать: Continue Reading