How to use PowerApps Table() Function - SPGuides (2023)

In this PowerApps functions tutorial, we will discuss how to use PowerApps Table() function. How to create a table using the PowerApps Table() function.

Also, we will see how to use GroupBy and Ungroup in PowerApps temporary table and how to create a nested table in PowerApps. Then we will see how to use Filter Function in PowerApps Table. Then we will see how to use PowerApps AddColumns(), DropColumns(), RenameColumns() and ShowColumns() function.

• Create a canvas app from Excel in Power Apps (Step by Step tutorial)
• How to add PowerApps app to Microsoft Teams

PowerApps table() function

PowerApps provides a Table() function which is used to create a temporary table in PowerApps desktop.

Syntax:

Table(Record1,Record2)

Each record we need to write inside a curly bracket. For example:

Table({ProductName:"MI Mobile",Price:13000, Review:"Good",Quantity:12}, {ProductName:"Lenovo Laptop",Price:4000, Review:"Average",Quantity:70} )

In the above Table function, I have created two records. Each record should be inside the “{}”. The fields of each record should be separated by “,”. We can assign the field value to the field by “:”.

In the above example “ProductName”, “Price”, “Review”, “Quantity” are called fields. Fields are combined called records. The “MI Mobile” is the field value I have assigned to “ProductName” fields.

For a single-column table, we do not need to write the Power Apps Table() function. We just simply need to mention the column names inside a square bracket and comma after each column’s name. For example [col1,col2,col3,…..].

Create a Table using PowerApps Table() function

In the below example I have displayed the temporary table data in a PowerApps Data Table control. First I have added a DataTable control from Insert->Data Table.

After the Data Table is successfully added to the PowerApps screen we will get the “DataTable1” name under the “Screen1” in the left side panel. Select the “DataTable1” and from the property dropdown select “Items” property. In the “Items” property Formula, bar write the below formula

Table({ProductName:"MI Mobile",Price:13000, Review:"Good",Quantity:12}, {ProductName:"Lenove Laptop",Price:4000, Review:"Average",Quantity:70},{ProductName:"MI TV",Price:30000, Review:"Bed",Quantity:20},{ProductName:"HP Laptop",Price:43000, Review:"Good",Quantity:90},{ProductName:"Dell Laptop",Price:12300, Review:"Very Good",Quantity:9})

In the DataTable control if you will not get the field then from the right-side panel under “Properties” you will get “Fields”. Select on the “Fields” you will get a Data Panel, you will get all the columns name. Check the columns name which you want to display in the DataTable.

Create a collection of records in PowerApps Table

Now we will see how to create a collection of records in PowerApps table. Two ways we can create a collection of Table Data.

First Method:

Add a Button control from Insert->Button. Button Control “OnSelect” property write

Set( ProductDetails, Table({ProductName:"MI Mobile",Price:13000, Review:"Good",Quantity:12}, {ProductName:"Lenove Laptop",Price:4000, Review:"Average",Quantity:70},{ProductName:"MI TV",Price:30000, Review:"Bed",Quantity:20},{ProductName:"HP Laptop",Price:43000, Review:"Good",Quantity:90},{ProductName:"Dell Laptop",Price:12300, Review:"Very Good",Quantity:9}) )

I have created a variable using the Set() keyword. The Variable name I have given “ProductDetails” and store the Table records. When we will click on the button the table records will save to the variable name.

See Also

Nuova funzionalità sperimentale: gestione degli errori e scrittura di valori null nei databaseIntelligenza artificiale nella sicurezza informatica | Una breve descrizioneChe cos'è il CPM nel marketing digitale: definizione, esempi

I have created one more button control. On the button “Onselect” property I have written “ClearCollect(DemoTable,ProductDetails)”. “DemoTable” is the collection name. I have assigned the variable name to the Collection name. When we will click on the button the collection will be created.

We can check the collection from the View->Collection.

Second Method:

Without using the variable name also we can create a collection. Just add one more button control “Onselect” property write the below rule.

ClearCollect(ProductCollection,Table({ProductName:"MI Mobile",Price:13000, Review:"Good",Quantity:12}, {ProductName:"Lenove Laptop",Price:4000, Review:"Average",Quantity:70},{ProductName:"MI TV",Price:30000, Review:"Bed",Quantity:20},{ProductName:"HP Laptop",Price:43000, Review:"Good",Quantity:90},{ProductName:"Dell Laptop",Price:12300, Review:"Very Good",Quantity:9}))

Display First record’s Field Value from PowerApps Table

We will see now how to display the ProductName column first record value. So I have added a Dropdown control from Insert -> Controls -> Dropdown.

On the Dropdown control Items property, I have added the rule “First(ProductDetails.ProductName)“. So in the dropdown, we will get the “MIMobile” value.

GroupBy and Ungroup PowerApps Temporary Table

We will see now what is PowerApps GroupBy() and Ungroup() function. The GroupBy() method is used to group a set of records based on one or more columns. Ungroup() method is used to ungroup the grouped items. We can understand better from the below example.

GroupBy():

I have added a button control set its text property to “TableVariable”. On the button “Onselect” I have written

Set(EmpDept,Table( { Department: "IT", Name: "Padmini"}, { Department: "IT", Name: "Preeti"}, { Department: "HR", Name: "Gita"}, { Department: "HR", Name: "Sumitra"}, { Department: "HR", Name: "Biswajeet"}, { Department: "Testing", Name: "Gayatri"}, { Department: "Testing", Name: "Tribeni"}, { Department: "Finance", Name: "Jasmini"}, { Department: "Finance", Name: "Lakshmi"} ))

I have just created a table and stored the table value in an EmpDept variable name.

(Video) Power Apps: Data Table

Now we will create a collection on a but “Onselect” property where we will store the Table value. So I have used the ClearCollect function and stored the variable name.

ClearCollect(EmployeeDepartment,EmpDept)

For “Group By” I have added one more button. Set the button text property to “GroupBy”. On the button control text propety, I have added

ClearCollect( DepartmentCollection, GroupBy(EmpDept , "Department", "Name" ) )

I have grouped the “Name” column based on the “Department” column. Inside the “groupBy” method I have passed the table variable name and columns name. I have stored the grouped item inside a collection. My collection name is “DepartmentCollection”.

Now we will check our output from View->Collections.

Ungroup():

Below is how we can use ungroup() function in PowerApps.

ClearCollect( DepartmentUngrouped, Ungroup( DepartmentCollection,"Name" ) )

PowerApps Nested Table

In the PowerApps we can create a temporary table inside another temporary table.

I have added a Button control, set its text property as “createcollection”. On the Button control “Onselect” property I have written the below rule

ClearCollect(ProductDetails,Table( {Product: "MI Mobile", 'Mobile Details': Table( { Name: "Redmi Y2", Price: 10000, Quantity: 10 }, { Name: "Redmi Y3",Price: 18000,Quantity: 9},{ Name: "Mi Redmi 6A",Price: 19000,Quantity: 90}) } ))

I have created a Table and stored the “Product” field value and inside the table I have created one more table and added some record value. Then I have stored the Table value in a collection named as “ProductDetails”.

We will get the collection from “View” tab->Collections. The Mobile Details contains a sub table.

When we will click on the “Table” icon we can see the sub-table all the records.

Embedded First() formula inside Table() in PowerApps

In the below example I have added a “First()” function inside the Table function. I have added a DataTable Control and on Its Item property I have added the below rule

Table({ EmployeeName: "Prashant", Salary:90000, Experience: 10, Address:"BTM"},{ EmployeeName: "Swarupa", Salary: 50000, Experience: 7, Address: "Marathalli"},{EmployeeName: "jyoti", Salary: 45000, Experience: 4, Address:"JPnager" },{ EmployeeName: "Manoranjan", Salary: 90000, Experience: 12, Address:"Kadugudi"})

I have added a Button control. On its “OnSelect” propety I have created a variable named as “EmpDetails” and stored the table value.

Now add one DataTable on its item property write

Table({ Name: First(EmpDetails).EmployeeName, NowSalary: First(EmpDetails).Salary+ (First(EmpDetails).Salary*2/10 )})

From the DataTable->right side panel(properties)->Fields. Check the field name.

In the above rule, I have added a Table() function. Inside the table function, I have written the First(). Add the Table value stored “variable name. the column name”.

Filter Function in PowerApps Table()

We will see how to use the filter function for PowerApps temporary table. First I have added a button control. Set its text property to “Create Table”. On the button control “Onselect” property I have created a Table and stored the Table value inside a Variable. The variable name is “StudentMarkSheet”.

Set(StudentMarkSheet,Table({Name:"Padmini",RollNumber:1,TotalMark:600,Mark:480},{Name:"Preeti",RollNumber:2,TotalMark:600,Mark:390},{Name:"Swarupa",RollNumber:3,TotalMark:600,Mark:150},{Name:"Sajal",RollNumber:4,TotalMark:600,Mark:400},{Name:"Sujit",RollNumber:5,TotalMark:600,Mark:250},{Name:"Saroj",RollNumber:9,TotalMark:600,Mark:420},{Name:"Linkan",RollNumber:10,TotalMark:600,Mark:299}))

Now I have added a Gallery control. Set its “Items” property to

Filter( StudentMarkSheet, Mark > 250 )

The Filter function will filter the “StudentMarkSheet” Table, where Mark column is more than 250. In the Gallery control, we will get the StudentName whose mark is more than 250.

PowerApps AddColumns(), DropColumns(), RenameColumns() and ShowColumns() function

AddColumns:

I have added one more Gallery control. Set its Items property to

AddColumns( StudentMarkSheet,"Percentage",Mark/TotalMark*100)

using AddColumns function we can add a new column in Table. In the below screenshot I have added a new column named as “Percentage” in the “StudentMarkSheet” Table. I have stored the “Mark/Total*100” value in the “Percentage” columns.

In the Gallery, control chooses “Title, Subtitle and Body” layout. Select on the body label on the label text property write “ThisItem.Percentage”.

ShowColumns:

Using The ShowColumns() we can display the selected columns in the PowerApps Control. Here I have created a DataTable, on the DataTable “Items” property I have written:

ShowColumns(AddColumns(StudentMarkSheet,"Percentage",Mark/TotalMark*100),"Name","Percentage")

In the below example I have added the new percentage columns and Name columns inside the ShowColumns().

RenameColumn(): Using the RenameColums() we can change the Tables old column name to new column name. So I have added the below rule in the DataTable Items property.

RenameColumns( StudentMarkSheet, "Name", "StudentName" )

DropColumn(): The DropColumns() is used to delete the column from the Table. In the below example I have added a “DataTable” control and in its Items property I have written:

DropColumns( StudentMarkSheet, "TotalMark")

I have deleted the “TotalMark” column. So in the DataTable fields property, we will not get the “Totalmark” column.

You may like following PowerApps tutorials:

• PowerApps upload file to SharePoint Online document library using Microsoft Flow
• PowerApps Employee Engagement Survey Example
• PowerApps Media Control
• Microsoft PowerApps Radio Button Example
• PowerApps submit form to SharePoint Online list
• Display SharePoint Online List Columns in multiple screens in Microsoft PowerApps
• Embed PowerApps in Modern SharePoint Online Site Page
• Microsoft PowerApps: Create Login Screen
• Microsoft PowerApps: Get Current Logged In User Details like Email ID, UserName in SharePoint Online
• PowerApps Sum function
• PowerApps Replace Function with examples
• Customize SharePoint List Forms with PowerApps

Conclusion

This PowerApps tutorial we learned:

• What is PowerApps Table() function.
• How to create a table using the PowerApps Table() function.
• How to use GroupBy and Ungroup in PowerApps temporary table.
• How to create a nested table in PowerApps.
• How to use Filter() Function in PowerApps Table.
• How to use PowerApps AddColumns(), DropColumns(), RenameColumns() and ShowColumns() function.