Skip to main content This browser is no longer supported. Show
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support. Understand tables and records in canvas apps
In this articleIn Power Apps, you can create a canvas app that accesses information in Microsoft Excel, SharePoint, SQL Server, and several other sources that store data in records and tables. To work most effectively with this kind of data, review the concepts that underlie these structures.
In your app, you'll use formulas to create, update, and manipulate records and tables. You'll probably read and write data to an external data source, which is an extended table. In addition, you might create one or more internal tables, which are called collections. You can build a variety of formulas that take the name of a table as an argument, just as a formula in Excel takes one or more cell references as arguments. Some formulas in Power Apps return a table that reflects the other arguments that you specify. For example, you might create a formula:
Elements of a tableRecordsEach record contains at least one category of information for a person, a place, or a thing. The example above shows a record for each product (Chocolate, Bread, and Water) and a column for each category of information (Price, Quantity on Hand, and Quantity on Order). In a formula, you can refer to a record by itself, outside of a table's context, by using curly braces. For example, this record { Name: "Strawberries", Price: 7.99 } isn't associated with a table. Note that field names, such as Name and Price in that example, aren't enclosed in double quotation marks. FieldsA field is an individual piece of information in a record. You can visualize this sort of field as a value in a column for a particular record. Just as with a control, you refer to a field of a record by using the . operator on the record. For example, First(Products).Name returns the Name field for the first record in the Products table. A field can contain another record or table, as the example for the GroupBy function shows. You can nest as many levels of records and tables as you want. ColumnsA column refers to the same field for one or more records in a table. In the above example, each product has a price field, and that price is in the same column for all products. The above table has four columns, shown horizontally across the top:
The column's name reflects the fields in that column. All values within a column are of the same data type. In the above example, the "Quantity on Hand" column always contains a number and can't contain a string, such as "12 units," for one record. The value of any field may also be blank. You may have referred to columns as "fields" in other tools. Note For data sources such as SharePoint, Excel, or Power BI tiles that contain column names with spaces, Power Apps will replace the spaces with "_x0020_". For example, "Column Name" in SharePoint, Excel, or Power BI tile will appear as "Column_x0020_Name" in Power Apps when displayed in the data layout or used in a formula. TableA table comprises one or more records, each with multiple fields that have consistent names across the records. Any table that's stored in a data source or a collection has a name, which you use to refer to the table and pass it to functions that take tables as arguments. Tables can also be the result of a function or a formula. As in the following example, you can express a table in a formula by using the Table function with a set of records, which you express in curly braces:
You can also define a single-column table with square brackets. An equivalent way to write the above:
Table formulasIn Excel and Power Apps, you use formulas to manipulate numbers and strings of text in similar ways:
In both cases, the calculated value changes automatically if you change the values of the arguments (for example, the number in cell A1 or the value of Slider1). Similarly, you can use formulas to access and manipulate data in tables and records. You can use names of tables as arguments in some formulas, such as Min(Catalog, Price) to show the lowest value in the Price column of the Catalog table. Other formulas provide whole tables as return values, such as RenameColumns(Catalog, "Price", "Cost"), which returns all the records from the Catalog table but changes the name of the Price column to Cost. Just as with numbers, formulas that involve tables and records are automatically recalculated as the underlying table or record changes. If the cost of a product in the Catalog table is lowered below the previous minimum, the return value of the Min formula will automatically change to match it. Let's walk through some simple examples.
Table functions and control propertiesConsider the Lower function. If the variable welcome contains the text string "Hello, World", the formula Lower( welcome ) returns "hello, world". This function doesn't, in any way, change the value in that variable. Lower is a pure function in that it only processes input and produces output. That's all; it has no side effects. All functions in Excel and most functions in Power Apps are pure functions, which allow the workbook or the app to be recalculated automatically. Power Apps offers a set of functions that operate on tables in the same manner. These functions take tables as input and filter, sort, transform, reduce, and summarize entire tables of data. In fact, Lower and many other functions that typically take a single value can also take a single-column table as input.
Many of these functions take a single-column table as their input. If an entire table has only one column, you can specify it by name. If a table has multiple columns, you can specify one of those columns by using Table.Column syntax. For example, Products.Name returns the single-column table of only Name values from the Products table. You can completely reshape a table however you want by using the AddColumns, RenameColumns, ShowColumns, or DropColumns function. Again, these functions change only their output, not their source. Properties of controls can also be tables:
Behavioral formulasOther functions are specifically designed to modify data and have side effects. Because these functions aren't pure, you must build them carefully, and they can't participate in automatically recalculating values in the app. You can use these functions only within behavioral formulas.
Record formulasYou can also build a formula that calculates data for an individual record, takes an individual record as an argument, and provides an individual record as a return value. Returning to our gallery example above, let's use the Gallery1.Selected property to display information from whatever record the user selects in that gallery.
This formula returns a record that includes not only the data from the record that's currently selected in the gallery but also each control in that gallery. For example, the record contains both a SampleText column, which matches the SampleText column in the original table, and a Subtitle1 column, which represents the label that shows the data from that column. Select the table icon in the Subtitle1 column to drill into that data. Note The Subtitle1 column might be named Subtitle2 or similar if you've added elements other than those that this topic specifies. Now that you have the selected record, you can extract individual fields from it with the . operator.
You've taken the Selected property, which is a record, and extracted the SampleHeading property from it. You can also use a record as a general-purpose container for related named values.
In these cases, the record was never a part of a table. Record functions and control propertiesFunctions that return records:
Properties that return records:
Record scopeSome functions operate by evaluating a formula across all the records of a table individually. The formula's result is used in various ways:
Inside these formulas, you can reference the fields of the record being processed. Each of these functions creates a "record scope" in which the formula is evaluated, where the fields of the record are available as top-level identifiers. You can also reference control properties and other values from throughout your app. For example, take a table of Products: To create this example table in your app, insert a button, set its OnSelect property to this formula, and then select the button (click it while you hold down the Alt key in Power Apps Studio):
To determine whether any of any of these products had more requested than is available:
The first argument to Filter is the table of records to operate on, and the second argument is a formula. Filter creates a record scope for evaluating this formula in which the fields of each record are available, in this case Product, Quantity Requested, and Quantity Available. The result of the comparison determines if each record should be included in the result of the function: Adding to this example, we can calculate how much of each product to order:
Here we are adding a calculated column to the result. AddColumns has its own record scope that it uses to calculate the difference between what has been requested and what is available. Finally, we can reduce the result table to just the columns that we want:
Note that in the above, we used double quotes (") in some places and single quotes (') in other places. Single quotes are required when referencing the value of an object, such as a field or table, in which the name of the object contains a space. Double quotes are used when we are not referencing the value of an object but instead talking about it, especially in situations in which the object does not yet exist, as in the case of AddColumns. DisambiguationField names added with the record scope override the same names from elsewhere in the app. When this happens, you can still access values from outside the record scope with the @ disambiguation operator:
If the table being operated upon is an expression, such as Filter( Table, ... ), then the disambiguation operator cannot be used. Only the innermost record scope can access fields from this table expression, by not using the disambiguation operator. For example, imagine having a collection X: You can create this collection with ClearCollect( X, [1, 2] ). And another collection Y: You can create this collection with ClearCollect( Y, ["A", "B"] ). In addition, define a context variable named Value with this formula: UpdateContext( {Value: "!"} ) Let's put it all together. In this context, the following formula:
produces this table: What is going on here? The outermost ForAll function defines a record scope for X, allowing access to the Value field of each record as it is processed. It can be accessed by simply using the word Value or by using X[@Value]. The innermost ForAll function defines another record scope for Y. Since this table also has a Value field defined, using Value here refers to the field in Y's record and no longer the one from X. Here, to access X's Value field, we must use the longer version with the disambiguation operator. Since Y is the innermost record scope, accessing fields of this table do not require disambiguation, allowing us to use this formula with the same result:
All the ForAll record scopes override the global scope. The Value context variable we defined isn't available by name without the disambiguation operator. To access this value, use [@Value]. Ungroup flattens the result because nested ForAll functions result in a nested result table. Single-column tablesTo operate on a single column from a table, use the ShowColumns function as in this example:
This formula produces this single-column table: For a shorter alternative, specify Table.Column, which extracts the single-column table of just Column from Table. For example, this formula produces exactly the same result as using ShowColumns.
Inline recordsYou express records by using curly braces that contain named field values. For example, you can express the first record in the table at the start of this topic by using this formula:
You can also embed formulas within other formulas, as this example shows:
You can nest records by nesting curly braces, as this example shows:
Enclose each column name that contains a special character, such as a space or a colon, in single quotes. To use a single quote within a column name, double it. Note that the value in the Price column doesn't include a currency symbol, such as a dollar sign. That formatting will be applied when the value is displayed. Inline tablesYou can create a table by using the Table function and a set of records. You can express the table at the start of this topic by using this formula:
You can also nest tables:
Inline value tablesYou can create single-column tables by specifying values in square brackets. The resulting table has a single column, named Value. For example, FeedbackSubmit and view feedback for What is a single piece of information that is stored in every record and represented by a column in a table?A formula. A single piece of information that is stored in every record and formatted as a column in a database table. A list of the field names in a table. Characteristics of a field that control how the field displays and how data can be entered in the field.
Is a single piece of information in a record in a database?field—A single piece of information in a record in a database. For example, in a phonebook record the fields would be: name, address, and phone number.
What is a principle stating that data is organized in tables so that there is no redundant data?Database normalization is the process of efficiently organizing data in a database so that redundant data is eliminated.
What data is a collection of information that is recorded in a table?A record is simply a set of data stored in a table, for example, a customer record. A record in a database is an object that can contain one or more values. Groups of records are then saved in a table; the table defines the data that each record may contain.
|