Таблицы

Заметка

Microsoft Power Fx — это новое название языка формул приложений на основе холста. Эти статьи находятся в стадии разработки, поскольку мы извлекаем язык из приложений на основе холста, интегрируем его с другими продуктами Microsoft Power Platform и делаем его доступным как открытый исходный код. Начнем с Обзор Microsoft Power Fx для введения в язык.

В Microsoft Power Fx вы можете написать формулу, которая обращается к информации в Microsoft Excel, SharePoint, SQL Server и нескольких других источниках, хранящих данные в записях и таблицах. Чтобы работать с данными такого типа максимально эффективно, ознакомьтесь с основными понятиями, которые лежат в основе этих структур.

• Запись содержит одну или несколько категорий сведений о человеке, месте или предмете. Например, запись может содержать имя, адрес электронной почты и номер телефона для одного клиента. В других средствах записи называются "строками" или "элементами".
• В таблице хранится одна или несколько записей, содержащих одинаковые категории сведений. Например, таблица может содержать имена, адреса электронной почты и номера телефона 50 клиентов.

Вы можете создавать различные формулы, которые принимают имя таблицы в качестве аргумента так же, как формула в Excel принимает одну или несколько ссылок на ячейки. Некоторые формулы в Power Fx возвращают таблицу, в которой отражаются значения других заданных аргументов. Например, можно создать формулу:

• чтобы обновлять записи в таблице, указав эту таблицу в качестве одного из нескольких аргументов функции Patch;
• чтобы добавлять, удалять и переименовывать столбцы в таблице, указав ее в качестве аргумента функции AddColumns, DropColumns или RenameColumns. Ни одна из этих функций не изменяет исходную таблицу. Вместо этого функция возвращает другую таблицу на основе других заданных аргументов.

Элементы таблицы

Записи

Каждая запись содержит по меньшей мере одну категорию сведений о человеке, месте или предмете. В предыдущем примере показана запись для каждого товара (Chocolate, Bread и Water) и столбец для каждой категории сведений (Price, Quantity on Hand и Quantity on Order).

Чтобы сослаться в формуле на саму запись вне контекста таблицы, воспользуйтесь фигурными скобками. Например, запись { Name: "Strawberries", Price: 7,99 } не связана с таблицей. Обратите внимание, что имена полей Name и Price в этом примере не заключаются в двойные кавычки.

Поля

Поле — это отдельная часть сведений в записи. Такого рода поля можно представить себе как значение в столбце определенной записи.

Как и в случае с элементом управления, для ссылки на поле записи используется оператор. для записи. Например, First(Products).Name возвращает поле Name для первой записи в таблице Products.

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

Столбцы

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

• Название
• Цена
• Количество в наличии
• Количество по заказу

Имя столбца соответствует содержащимся в нем полям.

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

В других средствах столбцы могут называться "полями".

Таблица

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

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

Как показано в следующем примере, таблицу в формуле можно представить с помощью функции Table с набором записей, которые записываются в фигурных скобках:

Table( { Value: "Strawberry" }, { Value: "Vanilla" } )

Используя квадратные скобки, можно также определить таблицу с одним столбцом. Эквивалентный способ представления приведенной выше записи:

[ "Strawberry", "Vanilla" ]

Формулы для таблиц

В Excel и Power Fx формулы для обработки чисел и строк текста используются аналогичным образом.

• Если в Excel ввести значение, например 42, в ячейке A1 и формулу, например A1+2, в другой ячейке, в ней отобразится значение 44.
• Если в Power Apps задать для свойства Default элемента управления Slider1 значение 42, а для свойства Text метки — значение Slider1.Value + 2, также отобразится значение 44.

В обоих случаях вычисленное значение изменяется автоматически при изменении значений аргументов (например, числа в ячейке A1 или значения Slider1).

Аналогично можно использовать формулы для доступа к данным в таблицах и записях, а также управления ими. В некоторых формулах имена таблиц можно использовать в качестве аргументов. Например, Min(Catalog, Price) можно использовать для отображения минимального значения столбца Price таблицы Catalog. Другие формулы возвращают в качестве значения целые таблицы, например RenameColumns(Catalog, "Price", "Cost"), которая возвращает все записи из таблицы Catalog, но изменяет имя столбца Price на Cost.

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

Функции для таблиц и свойства элементов управления

Рассмотрим функцию Lower. Если переменная welcome содержит текстовую строку "Hello, World", формула Lower( welcome ) возвращает "hello, world". Эта функция никоим образом не изменяет значение в этой переменной. Lower — это чистая функция в том смысле, что она только обрабатывает входные данные и производит выходные данные. Это все; она не имеет побочных эффектов. Все функции в Excel и большинство функций в Power Fx — это чистые функции, которые позволяют автоматически пересчитать книгу или приложение.

Power Fx предлагает набор функций, которые работают с таблицами таким же образом. Эти функции принимают таблицы в качестве входных данных и фильтруют, сортируют, преобразовывают, сокращают и суммируют целые таблицы данных. На самом деле функция Lower и многие другие функции, которые обычно принимают одно значение, также могут принимать в качестве входных данных таблицу из одного столбца.

Многие функции принимают в качестве входных данных таблицу с одним столбцом. Если во всей таблице есть только один столбец, вы можете указать его по имени. Если таблица имеет несколько столбцов, вы можете указать один из этих столбцов, используя синтаксис Таблица.Столбец. Например, Products.Name возвращает таблицу с одним столбцом только значений Name из таблицы Products.

Вы можете полностью изменить форму таблицы, как хотите, используя функцию AddColumns, RenameColumns, ShowColumns или DropColumns. Опять же, эти функции изменяют только свое выходные данные, но не источник.

Формулы поведения

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

Область действия записи

Некоторые функции при выполнении вычисляют формулы для всех записей таблицы по отдельности. Результат формулы используется различными способами:

• AddColumns — формула предоставляет значение добавленного поля.
• Average, Max, Min, Sum, StdevP, VarP — формула предоставляет значение для выполнения статистического вычисления.
• Filter, Lookup — формула определяет, нужно ли включать запись в выходные данные.
• Concat — формула определяет строки, которые следует объединять.
• DISTINCT — формула возвращает значение, используемое для выявления повторяющихся записей.
• ForAll — формула может возвращать любое значение, потенциально может иметь побочные эффекты.
• Sort — формула предоставляет значение для сортировки записей.
• With — формула может возвращать любое значение, потенциально может иметь побочные эффекты.

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

Например, возьмем таблицу Товары, помещенную в глобальную переменную:

Set( Products,
    Table(
        { Product: "Widget",    'Quantity Requested': 6,  'Quantity Available': 3 },
        { Product: "Gadget",    'Quantity Requested': 10, 'Quantity Available': 20 },
        { Product: "Gizmo",     'Quantity Requested': 4,  'Quantity Available': 11 },
        { Product: "Apparatus", 'Quantity Requested': 7,  'Quantity Available': 6 }
    )
)

Чтобы определить, требуется ли какого-либо из этих продуктов больше, чем есть в наличии, воспользуйтесь формулой:

Filter( Products, 'Quantity Requested' > 'Quantity Available' )

Первый аргумент функции Filter — это таблица записей, которые необходимо обработать, а второй — это формула. Функция Filter создает для вычисления этой формулы область записи, в которой доступны поля каждой записи. В данном случае это Product, Quantity Requested и Quantity Available. От результата сравнения зависит, нужно ли включать каждую запись в результат функции.

Просуммировав значения в этом примере, можно рассчитать, сколько единиц каждого товара следует заказать.

AddColumns( 
    Filter( Products, 'Quantity Requested' > 'Quantity Available' ), 
    "Quantity To Order", 'Quantity Requested' - 'Quantity Available'
)

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

Наконец, мы можем сократить результирующую таблицу только до необходимых столбцов.

ShowColumns(
    AddColumns(
        Filter( Products, 'Quantity Requested' > 'Quantity Available' ),
        "Quantity To Order", 'Quantity Requested' - 'Quantity Available'
    ),
    "Product",
    "Quantity To Order"
)

Обратите внимание, что в приведенном выше примере в одних местах мы использовали двойные кавычки ("), а в других — одинарные ('). Одинарные кавычки необходимы при ссылке на значение объекта, например поля или таблицы, если его имя содержит пробел. Двойные кавычки используются, когда нужно не сослаться на значение объекта, а просто его объявить, что особенно важно в ситуациях, когда объект еще не существует, как в случае с AddColumns.

Устранение неоднозначности

Имена полей, добавленные с помощью области записи, переопределяют такие же имена из любого другого места в приложении. В этом случае с помощью оператора устранения неоднозначности @ можно по-прежнему получать доступ к значениям за пределами области записи.

• Для доступа к значениям из вложенных областей записей используйте оператор @, указав имя нужной таблицы в формате:
  Table[@FieldName]
• Чтобы получить доступ к глобальным значениям, таким как источники данных, коллекции и переменные контекста, используйте шаблон [@ObjectName] (без обозначения таблицы).

Если таблица, над которой выполняется операция, представляет собой выражение, например Filter(Table, ... ), оператор устранения неоднозначности использовать нельзя. Не используя оператор устранения неоднозначности, получить доступ к полям этого табличного выражения можно только в наиболее глубоко вложенной области записи.

Представьте, например, что есть коллекция X.

Ее можно создать с помощью функции ClearCollect( X, [1, 2] ).

Есть также другая коллекция Y.

Эту коллекцию можно создать с помощью функции ClearCollect( Y, ["A", "B"] ).

Кроме того, определите переменную контекста с именем Value с помощью следующей формулы: UpdateContext( {Value: "!"} )

Давайте теперь соберем все это вместе. В этом контексте следующая формула:

Ungroup(
    ForAll( X,
        ForAll( Y,
            Y[@Value] & Text( X[@Value] ) & [@Value]
        )
    ),
    "Value"
)

дает такую таблицу:

Что в этом случае происходит? Внешняя функция ForAll определяет область записи для X, обеспечивая доступ к полю Value всех записей по мере их обработки. Для доступа к нему можно просто воспользоваться словом Value или выражением X[@Value].

Наиболее глубоко вложенная функция ForAll определяет другую область записи для Y. Поскольку в этой таблице также определено поле Value, значение Value в данном случае будет ссылаться на поле в записи коллекции Y и больше не будет ссылаться на поле из X. Чтобы получить здесь доступ к полю Value из коллекции X, необходимо использовать более длинную версию с оператором устранения неоднозначности.

Поскольку Y является самой глубоко вложенной областью записи, для доступа к полям в этой таблице устранение неоднозначности не требуется, что позволяет использовать эту формулу с тем же результатом:

Ungroup(
    ForAll( X,
        ForAll( Y,
            Value & Text( X[@Value] ) & [@Value]
        )
    ),
    "Value"
)

Все области записей ForAll переопределяют глобальную область. На определенную нами переменную контекста Value нельзя ссылаться по имени без оператора устранения неоднозначности. Чтобы получить доступ к этому значению, используйте [@Value].

Ungroup преобразует результат в плоскую структуру, поскольку вложенные функции ForAll приведут к созданию вложенной результирующей таблицы.

Таблицы с одним столбцом

Для работы с одним столбцом из таблицы используйте функцию ShowColumns, как в этом примере:

ShowColumns( Products, "Product" )

Эта формула производит эту таблицу с одним столбцом:

Для более короткой альтернативы укажите Table.Column, что извлекает таблицу из одного столбца просто из столбца Column из таблицы Table. Например, эта формула дает точно такой же результат, как и при использовании ShowColumns.

Products.Product

Встроенные записи

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

{ Name: "Chocolate", Price: 3.95, 'Quantity on Hand': 12, 'Quantity on Order': 10 }

Вы можете также встраивать одни формулы в другие, как показано в этом примере:

{ Name: First(Products).Name, Price: First(Products).Price * 1.095 }

Чтобы вложить записи, можно воспользоваться вложенными фигурными скобками, как показано в следующем примере:

{ 'Quantity': { 'OnHand': ThisItem.QuantOnHand, 'OnOrder': ThisItem.QuantOnOrder } }

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

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

Встроенные таблицы

Таблицу можно создать с помощью функции Table и набора записей. Таблицу, приведенную в начале данного раздела, можно представить с помощью следующей формулы:

Table( 
	{ Name: "Chocolate", Price: 3.95, 'Quantity on Hand': 12, 'Quantity on Order': 10 },
	{ Name: "Bread", Price: 4.95, 'Quantity on Hand': 34, 'Quantity on Order': 0 },
	{ Name: "Water", Price: 4.95, 'Quantity on Hand': 10, 'Quantity on Order': 0 } 
)

Таблицы можно также вкладывать друг в друга:

Table( 
	{ Name: "Chocolate", 
	  'Quantity History': Table( { Quarter: "Q1", OnHand: 10, OnOrder: 10 },
	                             { Quarter: "Q2", OnHand: 18, OnOrder: 0 } ) 
	}
)

Таблицы встроенных значений

Вы можете создать таблицы с одним столбцом, указав значения в квадратных скобках. Результирующая таблица будет содержать один столбец с именем Value.

Например, [ 1, 2, 3, 4 ] эквивалентно Table( { Value: 1 }, { Value: 2 }, { Value: 3 }, { Value: 4 } ) и возвращает следующую таблицу: