Как получить несколько значений в итоге запроса 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 .
У нас есть как минимум три метода это создать:
- Разбить запрос в подходящей точке на два – в одном получим ValueX , в другом, ссылающемся на него – ResultTable .
- Возвратить сходу несколько значений ( ResultTable и ValueX ) в виде записи либо перечня.
- Возвратить одно значение ResultTable , и ValueX в виде метаданных.
Разбить запрос на части
С первым методом всё, в общем, понятно. Для этого есть особая команда в меню по клику правой клавишей мыши в панели шагов Power Query: «Извлечь предшествующий»:
Извлечь прошлые шаги в новейший запрос
С помощью данной для нас команды все шаги до текущего будут выделены в новейший запрос, а все шаги начиная с текущего – останутся в прежнем запросе, при этом в качестве источника для прежнего запроса будет применено имя новейшего запроса. Имя для новейшего запроса будет задано при разделении. Таковым образом, мы можем отдать новенькому запросу имя GetValueX, и употреблять его итог как для продолжения преобразований, так и в качестве источника для остальных запросов.
Разделяем запросы на несколько
Этот подход – более верный и логичный, если мы можем конкретно употреблять ValueX для получения ResultTable (другими словами ValueX содержит всю нужную информацию для получения ResultTable ).
Если вы работаете лишь с пользовательским интерфейсом Power Query и не притрагиваетесь к строке формул либо расширенному редактору для написания собственных шагов запроса, на этом в большинстве случаев можно и тормознуть.
Но если промежный шаг, который мы желаем извлечь из запроса, содержит в для себя лишь часть подходящих данных, либо совершенно скалярное значение (к примеру, мы высчитали минимальную дату по столбцу, и в предстоящем планируем ее употреблять в остальных запросах) нам придется разбивать запрос мало ранее, и дублировать получение ValueX в различных запросах:
Чтоб не нарушалась цепочка вычислений, запросы нужно разбивать так, чтоб связанные могли без заморочек получить нужные данные
На этом рисунке зеленоватая ветвь обозначает наш главный запрос, который мы разбили на два новейших заблаговременно, до шага получения искомого значения ValueX . В красноватой ветке мы ссылаемся на Query1, чтоб получить отдельным запросом ValueX для использования в запросах Query2 и Query3.
А сейчас представьте, что нам необходимо получить к тому же ValueY , и ValueZ , и еще несколько промежных значений (что является полностью настоящим сценарием, если на входе у нас один большенный массив данных, из которого мы строим модель). Цепочка зависимостей запросов разрастается, что может привести к самым различным последствиям – от неудобства ориентирования в большенном документе и до количества воззваний Power Query к источнику данных. Время от времени этого избежать нереально (ну и не надо), но время от времени городить целый огород для того, чтоб пользоваться всего одним числом – совсем сверхизбыточно.
Возвратить итог в виде записи либо перечня
Этот вариант просит базисного познания главных составных типов данных Power Query и того, как обращаться к ним либо их частям.
По сути всё просто. Если у нас есть два значения, A и B, мы можем составить из их составной тип данных «перечень» (list):
В таком случае для получения значения А нам необходимо написать MyList < 0 >, а для получения B – MyList < 1 >, где число в фигурных скобках – индекс элемента, начиная с 0. Видите ли, порядок частей в перечне важен.
Если мы желаем обратиться к элементам не по порядку, а по какому-то имени, то мы можем употреблять составной тип «запись» (record):
Тогда для получения значения A нужно написать MyRecord [ Give_me_A ] , а для получения B – соответственно MyRecord [ Give_me_B ] . В этом случае порядок полей в записи уже не важен.
Пока мы не посчитали наш запрос совсем, мы можем ссылаться на хоть какой из его шагов по имени. Потому, к примеру, если крайний шаг в нашем запросе был
то мы можем добавить очередной шаг:
И так дальше: можно хоть все шаги запроса перечислить в записи либо перечне и обращаться к ним из остальных запросов, если для вас это для чего-то необходимо.
Результатом нашего запроса Query1 в этом случае будет уже не таблица, а запись. Чтоб сослаться на ее составные части в остальных запросах, мы будем уже писать так:
