Как получить несколько значений в результате запроса 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 .

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

  1. Разбить запрос в нужной точке на два – в одном получим ValueX , в другом, ссылающемся на него – ResultTable .
  2. Вернуть сразу несколько значений (ResultTable  и ValueX ) в виде записи или списка.
  3. Вернуть одно значение 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 , в том числе некоторую служебную информацию.

Вот так выглядит запись с метаданными запроса. Обратите внимание на служебное поле QueryFolding

Чтобы получить именно те данные, которые мы добавили сами, нам нужно уточнить поле мета-записи:

Source = Value.Metadata(Query1)[MidStep]

Мы получим именно искомое – значение ValueX , которое мы передали под именем MidStep .

Значение, записанное в поле метаданных, может быть получено другим запросом

Таким образом, мы можем без проблем выгружать запрос Query1 на лист или в модель данных – с точки зрения типа результирующего значения ничего не изменилось. Но внутри Power Query мы теперь имеем доступ также и к метаданным, которые при необходимости можем извлечь и использовать где угодно:

Основной результат ПЛЮС дополнительная информация в метаданных

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

Тонкости работы с метаданными

При помощи оператора meta  мы можем снабдить метаданными любое значение в запросе Power Query. Однако нужно учитывать некоторые тонкости:

  1. Использовать метаданные для передачи дополнительной информации между шагами внутри запроса практически бессмысленно – мы всегда можем записать эту информацию в отдельный шаг (переменную) и ссылаться на него в нужных местах запроса. Есть исключения, когда мы используем метаданные в некоторых функциях-итераторах для передачи дополнительной информации между итерациями, но это тема для отдельной статьи.
  2. При проведении каких-то операций над значениями, имеющими метаданные, мы получаем новое значение, которое, как правило, не наследует метаданные. Например, пусть у нас есть такой запрос:
    let
        A = 1, // значение без метаданных
        B = 2 meta [Тип = "четное"], // значение с метаданными
        С = A * B
    in
        C // метаданные не передались из B в C
  3. Результат запроса – это выражение, идущее после ключевого слова in  (даже если это выражение – просто ссылка на имя последнего шага запроса). Для передачи дополнительной информации из одного запроса в другой запрос необходимо, чтобы метаданными было снабжено именно то значение, которое является результатом запроса в целом:
        FinalResultWithMeta = Result meta [MetaTag = MetaValue]
    in
        FinalResultWithMeta

    или

        A = 1,
        B = 2
    in
        A * B meta [Оператор = "умножение"]
    
  4. Метаданные можно добавлять или заменять. Добавление производится так же посредством оператора 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, метаданных нет
  5. В записи, с помощью которой мы создаем метаданные, значением конкретного мета-поля может быть что угодно – число, текст, список, другая запись, таблица и т.п. Например:
    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: Facebooktwitterlinkedinmail
Share this: Facebooktwitterredditpinterestlinkedinmail
Share this
Объединяем выбор на разных срезах в DAX: Динамический фильтр “ИЛИ”
Номер недели ISO в Power Query / M и Power BI