Как получить несколько значений в результате запроса 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 , и мы хотели бы использовать ее в других выражениях или запросах:
// Query1 let Source = …, // источник данных …, // какие-то шаги ValueX = …, // промежуточный результат …, // еще какие-то шаги ResultTable = … // получаем таблицу-результат in 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 = {A, B}
В таком случае для получения значения А нам нужно написать MyList{0} , а для получения B – MyList{1} , где число в фигурных скобках – индекс элемента, начиная с 0. Как видите, порядок элементов в списке важен.
Если мы хотим обратиться к элементам не по порядку, а по какому-то имени, то мы можем использовать составной тип “запись” (record):
MyRecord = [ Give_me_A = A, Give_me_B = B]
Тогда для получения значения A надо написать MyRecord[Give_me_A] , а для получения B – соответственно MyRecord[Give_me_B] . В этом случае порядок полей в записи уже не важен.
Пока мы не посчитали наш запрос окончательно, мы можем ссылаться на любой из его шагов по имени. Поэтому, например, если последний шаг в нашем запросе был
ResultTable = …,
то мы можем добавить еще один шаг:
FinalResult = [MainResult = ResultTable, MidStep = ValueX]
И так далее: можно хоть все шаги запроса перечислить в записи или списке и обращаться к ним из других запросов, если вам это зачем-то нужно.
Результатом нашего запроса Query1 в этом случае будет уже не таблица, а запись. Чтобы сослаться на ее составные части в других запросах, мы будем уже писать так:
GetTableFromQuery1 = Query1[MainResult], // таблица-результат запроса Query1 GetValueXFromQuery1 = Query1[MidStep], // промежуточный шаг из запроса Query1
Этот способ вполне хорош, кроме одного большого «НО»: он годится только в том случае, если сам по себе ResultTable не будет выводиться как таблица на лист или в модель данных. Иначе нам придется написать еще один запрос, теперь уже к Query1, для того, чтобы просто вытащить упомянутый ResultTable в виде таблицы.
Используй meta, Люк
В Power Query к любому рассчитанному значению можно добавить дополнительную информацию в виде метаданных (метаданные – информация об информации, или дополнительная информация о каких-то данных). С концепцией метаданных в Power Query мы встречаемся постоянно, когда читаем описание функций в интерфейсе программы: текстовые описания функций и их параметров, а также примеры использования – всё это прикреплено к самой функции в виде метаданных.
Самый простой способ добавить метаданные к какому-то значению – это использовать оператор meta . С его помощью мы можем к любому рассчитанному значению добавить метаданные в виде записи (пар «поле = значение»). Синтаксис его использования очень простой:
FinalResult = ResultTable meta [MidStep = ValueX]
В этом примере мы рассчитываем значение FinalResult (которое равно значению выражения ResultTable ), а данные промежуточного шага ValueX будут добавлены к нему в виде метаданных. Внешне мы не увидим никаких изменений – результатом запроса по-прежнему будет таблица ResultTable ,
а значение промежуточного шага не будет видно и доступно, пока мы не запросим его d в другом запросе специальной функцией:
Source = Value.Metadata(Query1)
Результатом этой функции будет запись, представляющая собой все метаданные значения FinalResult , в том числе некоторую служебную информацию.
Чтобы получить именно те данные, которые мы добавили сами, нам нужно уточнить поле мета-записи:
Source = Value.Metadata(Query1)[MidStep]
Мы получим именно искомое – значение ValueX , которое мы передали под именем MidStep .
Таким образом, мы можем без проблем выгружать запрос Query1 на лист или в модель данных – с точки зрения типа результирующего значения ничего не изменилось. Но внутри Power Query мы теперь имеем доступ также и к метаданным, которые при необходимости можем извлечь и использовать где угодно:
Хотя изначально метаданные предполагают расширение свойств и характеристик основного значения, то есть как бы описательную функцию, Power Query позволяет нам передавать в качестве метаданных что угодно – от скалярной величины, введенной вручную, до сложных составных типов, совершенно не связанных с основным значением.
Тонкости работы с метаданными
При помощи оператора meta мы можем снабдить метаданными любое значение в запросе Power Query. Однако нужно учитывать некоторые тонкости:
- Использовать метаданные для передачи дополнительной информации между шагами внутри запроса практически бессмысленно – мы всегда можем записать эту информацию в отдельный шаг (переменную) и ссылаться на него в нужных местах запроса. Есть исключения, когда мы используем метаданные в некоторых функциях-итераторах для передачи дополнительной информации между итерациями, но это тема для отдельной статьи.
- При проведении каких-то операций над значениями, имеющими метаданные, мы получаем новое значение, которое, как правило, не наследует метаданные. Например, пусть у нас есть такой запрос:
let A = 1, // значение без метаданных B = 2 meta [Тип = "четное"], // значение с метаданными С = A * B in C // метаданные не передались из B в C
- Результат запроса – это выражение, идущее после ключевого слова in (даже если это выражение – просто ссылка на имя последнего шага запроса). Для передачи дополнительной информации из одного запроса в другой запрос необходимо, чтобы метаданными было снабжено именно то значение, которое является результатом запроса в целом:
FinalResultWithMeta = Result meta [MetaTag = MetaValue] in FinalResultWithMeta
или
A = 1, B = 2 in A * B meta [Оператор = "умножение"]
- Метаданные можно добавлять или заменять. Добавление производится так же посредством оператора meta (в соответствии с принципами объединения записей), а замена – при помощи специальной функции Value.ReplaceMetadata :
let A = 1 meta [NumberType = "odd"], // значение = 1, метаданные: [NumberType = "odd"] B = A meta [Color = "Black"], // значение = 1, метаданные: [NumberType = "odd", Color = "Black"] С = Value.ReplaceMetadata(B, [Length = 5]) // значение = 1, метаданные: [Length = 5] in C * 2 // значение = 2, метаданных нет
- В записи, с помощью которой мы создаем метаданные, значением конкретного мета-поля может быть что угодно – число, текст, список, другая запись, таблица и т.п. Например:
let A = 1, B = “text”, C = {A, B} in "result" meta [Ameta = A, Bmeta = B, Cmeta = C]
Использование метаданных значительно расширяет возможности работы с данными и запросами в Power Query. В некоторых случаях метаданные – гораздо более удобный способ передачи составной информации, чем другие традиционные методы. Самое прагматичное и эффектное применение метаданных, которое мне пока встречалось – в функциях типа List.Generate при работе с многостраничными запросами к API (т.е. paginated API response). Об этом будет отдельный пост.
Follow me:Share this: