Эта статья – перевод моего первого поста в этом блоге, который был опубликован 5 ноября 2015 года на английском языке. К моему удивлению, этот пост – самый популярный, за это время он набрал почти 21000 просмотров. С небольшими стилистическими правками публикую его на русском языке. В переводе помогал мой сын Дмитрий, за что ему отдельное спасибо.
Power Query – это мощный инструмент, способный на большее, чем просто брать данные из источника и переносить их в таблицу или Power Pivot. Данные можно очищать и преобразовывать множеством способов, но есть некоторые действия, привычные для Excel, которые не так удобно делать в Power Query.
Например, что, если мне нужна относительная ссылка на конкретную ячейку в таблице Power Query – значение из определённой строки в определённом столбце? Или ссылка на значение в определённом столбце на четыре строки выше? В Excel очевидно, как это сделать: нужно просто указать на нужное значение мышкой, убедиться, что из ссылки к строке убран знак “$” (знак абсолютной ссылки), и всё. Но в Power Query я не могу так просто это сделать.
Но всё же решение, хотя и непрямое, существует.
Прежде всего давайте выясним, как можно получить доступ к конкретному значению из таблицы Power Query.
Простейший способ понять адресацию в Power Query, по-моему, анализировать код шагов.
Абсолютные ссылки на строки
Допустим, у нас есть простая таблица из двух столбцов: даты (Date) и количества (Amount). В ней пять строк, и в первом столбце стоят, как ни странно, даты, во втором – какие-то значения:
Исходные данные
Мы хотим получить значение из ячейки B4, а именно 120. Continue Reading →
Follow me: 
Недавно мне нужно было сделать очень простую операцию в Power Query. В столбце с числами нужно было выполнить проверку “значение меньше N” и в новом столбце вывести соответствующий текст. Функция дополнительного столбца выглядит примерно так:
= if [Values] < 5 then "A" else "B"
На самом деле некоторые значения – null (то есть пустые):
Данные содержат null и в результате сравнения возникает ошибка
И такая простая операция возвращает ошибку для этих значений!
Почему? Есть некоторая ловушка, спрятанная в глубинах документации (а именно на странице 67 PDF-файла “Power Query Formula Language Specification (October 2016)”, который можно найти тут.
Continue Reading →
Follow me:
Эта статья о работе надстройки Power Query к Excel 2010 и Excel 2013, редактора запросов Power BI и группы команд “Get & Transform” (“Получить и преобразовать”) в Excel 2016. Надеюсь, когда-нибудь эта чехарда закончится и мы сможем говорить просто Power Query.
Результатом вычисления запроса в Power Query является единственное значение. Как правило, речь идет о таблице, которую мы затем выгружаем на лист или в модель данных в Excel и Power BI. Это же требование относится также и к любым другим выражениям, вычисляемым Power Query, например, встроенным или пользовательским функциям, полям записей, записям в целом, и т.д.
Однако можно легко представить себе ситуацию, когда нам нужно, чтобы функция или запрос вместе с основным результатом вернули и другую информацию – дополнительный или промежуточный результат вычисления.
Промежуточные и дополнительные результаты запросов
Представьте, что в процессе сложных преобразований запроса Query1 последним шагом под названием ResultTable мы получили нужный результат (таблицу), которую мы хотим загрузить в модель данных. Одним из промежуточных шагов в нашем запросе был расчет какой-то величины ValueX , и мы хотели бы использовать ее в других выражениях или запросах:
// Query1
let
Source = …, // источник данных
…, // какие-то шаги
ValueX = …, // промежуточный результат
…, // еще какие-то шаги
ResultTable = … // получаем таблицу-результат
in
ResultTable // но она не содержит в себе данные для получения ValueX
Нам в итоге нужен и ValueX , и, конечно же, ResultTable .
У нас есть как минимум три способа это сделать: Continue Reading →
Follow me:
