Вложенные выражения: среды Power Query

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

Оглавление

• Создание вложенных выражений
• Понимание вложенных выражений в среде
• Решение общих сценариев
  • Использование переменных для хранения значений
  • Получение одного значения
• Заключение

Создание вложенных выражений

Выражения определяются как формулы, используемые для построения значения.

Выражение в строке формул состоит из подвыражений. Литералы 1 и 2 являются подвыражениями родительского выражения.

Переменные определяются как именованные значения. Это образец записи.

Если вы откроете окно расширенного редактора, вы увидите переменные, используемые в запросе.

Каждая из переменных a , b и c представляет собой значение, которое является результатом выражения, которое вы часто найдете после знака равенства. Все переменные в родительском выражении, которое является самой записью, формируют среду этой записи. В этой среде каждая переменная должна быть уникальной.

Поэтому, если вы добавите еще одну переменную с именем c , вы увидите сообщение об ошибке под окном расширенного редактора.

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

В этом примере вы можете видеть, что переменная c ссылается сама на себя.

Если вы нажмете «Готово», вы получите сообщение об ошибке.

Если вы извлечете значение c , добавив оператор доступа к элементу, вы получите результат. Вы также можете получить тот же результат с выражением let , потому что к нему применяются те же правила.

Понимание вложенных выражений в среде

Это еще один пример рекордного значения.

Если вы откроете окно расширенного редактора, вы увидите, что переменные a и b используют одну и ту же среду, которая является самой внешней средой. Вы также увидите, что переменные x , y и z являются подвыражениями родительского выражения a .

Каждая переменная может получить доступ ко всем другим переменным во вложенной записи, кроме самой себя. Вы можете вызвать переменную, которая существует в другой среде. В этом примере вы можете видеть, что z ссылается на b , который находится во внешней среде.

Переменные x , y и z могут получить доступ к b , потому что b является частью их родительской среды выражения. Вы также можете иметь одну и ту же переменную, но в разных средах.

В этом другом примере вы можете видеть, что внутренняя и внешняя записи имеют переменную x .

Переменные должны быть уникальными в своей среде. Будет использоваться ближайший x, присвоенный переменной. В этом примере запроса используется внутренний x , так как он ближе с точки зрения записи a .

В этом другом запросе в выражении используется ближайший x , близкий к переменной b .

Внешняя переменная b может ссылаться на переменную x внутри записи a с помощью оператора доступа к элементу. Вам нужно сослаться на переменную a , а затем получить доступ к x , используя набор квадратных скобок. Точно так же запись a может ссылаться на переменную x внутри переменной b .

Когда среда вложенной записи a объединяется с переменными из родительского выражения, это приводит к конфликту, поскольку x существует дважды, и все переменные должны быть уникальными в своем окружении. Поскольку переменная не может ссылаться на себя, конфликт разрешается обращением к внешнему x.

Решение общих сценариев

Обычный сценарий — преобразование вложенных объектов. Для демонстрации будет использована эта таблица.

Если вы хотите получить значение из внешней таблицы и поместить его во вложенную таблицу, это можно сделать двумя способами. Первый способ заключается в использовании параметра «Добавить пользовательский столбец» .

Щелкните значок таблицы в верхнем левом углу панели предварительного просмотра и выберите «Добавить пользовательский столбец».

Введите Temp в качестве имени столбца и введите заполнитель в формулу пользовательского столбца . После этого нажмите ОК.

В строке формул замените каждое выражение пользовательской функцией, добавив столбец во вложенную таблицу. Запустите логику пользовательской функции и назовите ее OT для внешней таблицы. Затем добавьте знак перехода.

Затем используйте функцию Table.AddColumn , чтобы добавить столбец. См. таблицу во внешней таблице в столбце «Имена». Затем введите новое имя столбца.

В этом примере используется имя столбца Revenue . Введите функцию each , чтобы получить значения каждой строки в столбце Get Revenue.

Если щелкнуть пробел внутри ячейки строки в столбце Temp, вы получите предварительный просмотр вложенной таблицы под панелью предварительного просмотра. Вы увидите, что каждая строка имеет значение внешней таблицы.

Использование переменных для хранения значений

Второй метод преобразования вложенных объектов заключается в использовании переменных для хранения значений. Сначала добавьте пользовательский столбец и введите Temp2 в качестве имени столбца. Для формулы используйте выражение let для сохранения значений в переменных.

Введите let , а затем напишите имя переменной. В этом примере имя переменной — myR . Затем приравняйте переменную к столбцу «Получить доход». После этого введите предложение in и используйте функцию Table.AddColumn , чтобы добавить столбец во вложенную таблицу.

В качестве первого параметра функции введите столбец Names, который содержит вложенные таблицы. Затем назовите новый столбец «Доход». Используйте each для вызова переменных каждой строки.

Если вы щелкните пробел внутри ячейки строки в столбце Temp2, вы увидите, что он извлекает значения из внешней таблицы.

Другим распространенным сценарием является поиск без общего ключа. Для этого сценария будут использоваться 2 примера таблиц: таблица категорий и таблица продуктов .

Используйте таблицу категорий в качестве промежуточного запроса, чтобы дополнить атрибуты таблицы измерений продукта. Поскольку общего ключа нет, слияние выполнить невозможно. Первое, что нужно сделать, это вложить полную таблицу категорий в запрос таблицы продуктов с помощью команды «Добавить пользовательский столбец».

Добавьте пользовательский столбец и напишите «Категория» в качестве имени столбца. Затем в пользовательской формуле вызовите запрос категории.

После этого вы вложили полную таблицу категорий в каждую строку таблицы продуктов.

Если щелкнуть пробел внутри ячейки строки в столбце «Категория», вы увидите полную таблицу категорий в каждой строке.

Получение одного значения

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

Откройте диалоговое окно «Пользовательский столбец», нажав кнопку с изображением шестеренки рядом с «Добавленный пользовательский столбец» на панели «Примененные шаги».

В формулу добавьте функцию Table.SelectRows . Для первого параметра используется таблица категорий. Второй параметр использовал функцию Text.Contains в качестве условия. Для внутренней таблицы эта функция проверяет, содержит ли текст часть строки продукта во внешней таблице.

При доступе к внешней таблице вы заметите, что Power Query добавляет ключевое слово each в строку формул. Это унарная функция, которая принимает знак подчеркивания ( _ ) как безымянную переменную. Однако, поскольку вы обращаетесь только к столбцу, его можно опустить.

Если щелкнуть пробел внутри ячейки строки в столбце «Категория», теперь вы увидите одну строку в каждой из вложенных таблиц.

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

Вернитесь в диалоговое окно «Пользовательский столбец» и добавьте операторы в формулу. Чтобы получить первую строку таблицы, введите 0 внутри набора фигурных скобок. Затем укажите название поля Категория в квадратных скобках.

Чтобы решение было ориентировано на будущее, вы можете добавить вопросительный знак ( ? ) в конце, чтобы изменить поведение не найдено с возврата ошибки на нуль.

Как работает редактор запросов в LuckyTemplates
M Language и редактор Power Query в LuckyTemplates

Заключение

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

Мелисса