Таблиці

У Microsoft Power Fx можна написати формулу для отримання доступу до інформації в Microsoft Excel, SharePoint, SQL Server та кількох інших джерелах, які зберігають дані в записах і таблицях. Щоб ефективніше працювати з такими даними, перегляньте концепції, які лежать в основу цих структур.

• Запис містить одну або кілька категорій відомостей про особу, місце або річ. Наприклад, у записі може міститись ім’я, адреса електронної пошти та номер телефону одного клієнта. Інші засоби посилаються на запис як на «рядок» або «елемент».
• Таблиця містить один або кілька записів, які містять однакові категорії інформації. Наприклад, у таблиці можуть міститися імена, адреси електронної пошти та номери телефонів 50 клієнтів.

Можна створювати різноманітні формули, які прийматимуть ім’я таблиці як аргумент, подібно до формул в Excel, що використовують одне або кілька посилань на клітинки як аргументи. Деякі формули у Power Fx повертають таблицю, що відображає інші аргументи, які ви вказали. Наприклад, можна створити таку формулу:

• щоб оновити запис у таблиці, указавши цю таблицю як один із кількох аргументів для функції Patch
• щоб додати, вилучити або перейменувати стовпці в таблиці, указавши цю таблицю як аргумент для функції AddColumns, DropColumns або RenameColumns. Жодна з цих функцій не змінює вихідну таблицю. Натомість ця функція повертає іншу таблицю відповідно до інших аргументів, які було задано.

Елементи таблиці

Записи

Кожен запис містить щонайменше одну категорію відомостей щодо особи, місця або речі. У наведеному вище прикладі показано запис для кожного продукту (Шоколад, Хліб та Вода) та колонки для кожної категорії інформації (Ціна, Кількість на руках і Кількість за замовленням).

У формулах можна посилатися на запис за його ім’ям за межами контексту таблиці, використовуючи фігурні дужки. Наприклад, запис { Назва: "Полуниця", Ціна: 7,99 } не зв’язаний з таблицею. Зверніть увагу на те, що імена полів, як-от Ім’я та Ціна у цьому прикладі, не ставляться у подвійні лапки.

Поля

Поле — це окрема одиниця інформації у записі. Ви можете уявити такий тип поля, як значення у стовпці для певного запису.

Так само, як і у випадку із елементом керування, ви посилаєтеся на поле запису, використовуючи .оператор у записі. Наприклад, First(Продукти).Ім’я повертає поле Ім’я для першого запису в таблиці Продукти.

Поле може містити інший запис або таблицю, як продемонстровано у прикладі для функції 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(Каталог, Ціна) покаже найменше значення в стовпці Ціна таблиці Каталог. Інші формули можуть повертати цілі таблиці, наприклад, функція RenameColumns(Каталог, "Ціна", "Вартість"), яка повертає усі записи з таблиці Каталог, але змінює ім’я стовпця Ціна на Вартість.

Так само, як і з числами, формули, у яких використовуються таблиці та записи, автоматично переобчислюються, коли ці таблиці або записи змінюються. Якщо вартість продукту в таблиці Каталог впаде нижче за попереднє мінімальне значення, то значення, що повертає формула Min, автоматично зміниться на новий мінімум.

Функції таблиць і властивості елементів керування

Розглянемо функцію Lower. Якщо змінна привітання містить текстовий рядок "Вітаю, Світе", формула Lower( привітання ) поверне "Вітаю, Світе". Ця функція у жодному разі не буде змінювати значення цієї змінної. Lower є чистою функцією, оскільки вона лише обробляє вхідні дані та виробляє вихід. Це все; вона не має побічних ефектів. Усі функції в Excel і більшість функцій у Power Fx — це чисті функції, які дають змогу автоматично повторно обчислювати значення у робочій книзі або програмі.

Power Fx пропонує набір функцій, які працюють із таблицями таким самим чином. Ці функції приймають таблиці як вхідні відомості, а потім фільтрують, сортують, змінюють, скорочують цілі таблиці із даними, або підбивають для них підсумки. Фактично, функція Lower та багато інших функцій, які зазвичай займають одне значення, можуть також приймати в якості вхідних відомостей таблицю із одним стовпцем.

Для більшості функцій вхідними даними є таблиці з одним стовпцем. Якщо вся таблиця має лише один стовпець, можна вказати його за іменем. Якщо у таблиці кілька стовпців, можна вказати один із цих стовпців, використовуючи синтаксис Таблиця.Стовпець. Наприклад, Продукти.Назва повертає таблицю з одним стовпцем лише для значень Назва з таблиці Продукти.

За допомогою функцій AddColumns, AddColumns ShowColumns та DropColumns можна повністю змінити вигляд таблиці на власний розсуд. Знову ж таки, ці функції змінюють лише власні вихідні відомості, а не джерело.

Формули поведінки

Інші функції спеціально призначені для того, щоб змінювати даних і мають побічні ефекти. Оскільки ці функції не є чистими, слід обережно добирати для них аргументи, а значення цих функцій не переобчислюватимуться автоматично разом із іншими значеннями у програмі. Ці функції можна використовувати лише в межах формул поведінки.

Область застосування записів

Деякі функції працюють, обробляючи окремо кожний запис таблиці окремо. Результат такої формули може використовуватись різними способами.

• AddColumns - формула надає значення доданого поля.
• Формула середнього, Max, Min, Sum , StdevP, VarP- надає значення для агрегування.
• Фільтр, Підстановка - Формула визначає, чи слід включати запис у вихідні дані.
• Concat - Формула визначає рядки, які потрібно об’єднати разом.
• Distinct - формула повертає значення, яке використовується для виявлення повторюваних записів.
• ForAll - Formula може повертати будь-яке значення, потенційно з побічними ефектами.
• Сортування - Формула надає значення для сортування записів.
• 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 — це таблиця записів, що оброблятимуться, а другий аргумент — це формула. Фільтр створює область запису для оцінки цієї формули, в якій доступні поля кожного запису, в даному випадку «Товар», «Кількість запитана» та «Доступна кількість». Результат порівняння визначає, чи слід включати кожен запис в результат функції.

Доповнюючи приклад, можемо підрахувати, скільки слід замовити кожного продукту.

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.

Усунення неоднозначності

Імена полів, додані в область застосування записів, перевизначають такі ж імена в іншому місці програми. Якщо це станеться, можна все одно отримати доступ до значень, які не належать до області застосування запису, використовуючи оператор усунення неоднозначності @.

• Щоб отримати доступ до значень з вкладених областей застосування записів, скористайтеся оператором @ з іменем таблиці, до якої оператор застосовується, згідно з цим прикладом:
  Таблиця[@НазваПоля]
• Щоб отримати доступ до глобальних значень, наприклад джерел даних, колекцій і контекстних змінних, скористайтеся шаблоном [@НазваОбєкта] (без зазначення таблиці).

Якщо таблиця, із якою ми працюємо, задана у вигляді виразу, наприклад Filter(Таблиця, ... ), то не можна використовувати оператор усунення неоднозначності. Отримати доступ до полів такого виразу таблиці, не використовуючи оператор усунення неоднозначності, можна лише з внутрішньої області застосування запису.

Наприклад, уявіть, що ви маєте колекцію 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].

Розгрупування зводить результат, оскільки вкладені функції ForAll призводять до вкладеної таблиці результатів.

Таблиці з одним стовпцем

Щоб працювати з одним стовпцем з таблиці, скористайтеся функцією ShowColumns, як показано у наведеному прикладі.

ShowColumns( Products, "Product" )

Ця формула створює таблицю з одним стовпцем, яку наведено нижче.

Коротше цього можна досягти, вказавши Таблиця.Стовпець, і таким чином буде отримано таблицю із єдиним стовпцем Стовпець з таблиці Таблиця. Наприклад, ця формула дає точно такий самий результат, як і використання 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 } }

Заключіть кожне ім’я стовпця із спеціальним символом, таким як пробіл або двокрапка, в одинарні лапки. Щоб використати символ однієї з одинарних лапок у імені, вкажіть його двічі.

Зверніть увагу на те, що значення у стовпці Ціна зазначено без символу грошової одиниці, наприклад, знака долара. Це форматування буде застосовано під час відображення значення.

Вбудовані таблиці

Таблицю можна створити за допомогою функції 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 } ) і повертає зазначену нижче таблицю.