Microsoft таблицы Power Fx

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

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

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

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

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

Записи

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

Чтобы сослаться в формуле на саму запись вне контекста таблицы, воспользуйтесь фигурными скобками. Например, запись { 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 или с помощью X[@Value].

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

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

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

Все области записей ForAll переопределяют глобальную область. Определенная контекстная переменная значения недоступна по имени без оператора диамбигуации. Чтобы получить доступ к этому значению, используйте [@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 } ) и возвращает следующую таблицу: