How to use PowerApps Table() Function

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.

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 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})
powerapps table function
powerapps table function

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.

PowerApps Table() function
powerapps table function

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.

create a table using the PowerApps Table() function
powerapps table function

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.

GroupBy and Ungroup in PowerApps
powerapps table function

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

powerapps collection function
powerapps collection function

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}))
create a nested table in PowerApps
powerapps collection function

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.

PowerApps Table() function
powerapps table function

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.

PowerApps AddColumns()

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)
Filter Function in PowerApps Table

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”.

Filter() Function in PowerApps Table
groupby in powerapps

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

powerapps groupby collection
powerapps groupby collection
create a nested table in PowerApps
powerapps groupby collection

Ungroup():

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

ClearCollect( DepartmentUngrouped, Ungroup( DepartmentCollection,"Name" ) )
powerapps ungroup function
powerapps ungroup
powerapps ungroup
powerapps ungroup

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”.

powerapps nested table
powerapps nested table

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

powerapps table function
powerapps nested table

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

powerapps table functions
powerapps nested table

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"})
powerapps first function
powerapps first function

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

powerapps table filter
powerapps first function

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”.

powerapps filter table data
powerapps first function

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}))
powerapps data table filter items
powerapps filter function in table values

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 data table filter items
powerapps filter function in table values

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”.

powerapps table add columns
powerapps add columns

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().

powerapps table show columns
powerapps show columns

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" )
powerapps table rename columns
powerapps rename columns

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.

powerapps table function
powerapps drop columns

You may like following PowerApps tutorials:

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.
>