How to Use Power Apps AddColumns Function? [With Examples]

I have recently worked on the PowerApps addcolumns function, which allows you to add a single column or multiple columns to the Power Apps collection.

In this Power Apps article, I will explain what the Power Apps AddColumns Function is, how to use Power Apps AddColumns function, Power Apps add column to collection, and Power Apps add multiple columns to collection.

Also, we will discuss the below topics. Such as:

  • Working with Power Apps add column to existing collection or Power Apps addcolumns lookup
  • Power Apps addcolumns filter
  • Power Apps addcolumns sum

PowerApps AddColumns Function

In Power Apps, the AddColumns function allows you to dynamically add new columns to a table based on calculations, conditions, or existing data in other columns.

It helps to add a column to a table, and the specified formula specifies the values in that column, whereas existing columns remain unmodified.

In Power Apps, a table defines a value like a string or a number. We can specify the table as an argument in a formula, and functions can return a table as a result.

Power Apps AddColumns Syntax:

AddColumns( Table, ColumnName1, Formula1 [, ColumnName2, Formula2, ... ] )

Where,

  • Table = This is required. It specifies the table to operate on
  • ColumnName = This is also required. Name(s) of the column(s) to add. You must specify a string (for example, “Name” with double quotes included) for this argument
  • Formula = This is required. Formula(s) to evaluate for each record. The result is added as the value of the corresponding new column. You can reference other columns of the table in this formula

How to Use Power Apps AddColumns Function

I have a SharePoint Online list named “Product Details,” which contains the below fields.

Column NameData Type
IDIt is a default ID column
Product NameIt is a default single line of text column
ManufacturerChoice
PriceCurrency
QuantityNumber
Is AvailableYes/No
power app addcolumns

Now, I would like to display these SharePoint list records on the data table, including a new column [Total Price] using the AddColumns() function. Have a look at the below screenshot for the output.

power app add column to collection

To do so, follow the below steps. Such as:

See also  Power Apps Modern Progress Bar [Detailed Guide]

1. To create a Power Apps collection, select the App object and set its OnStart property to the code below.

OnStart = ClearCollect(colProducts,'Product Details')

Where,

  • colProducts = Power Apps collection name
  • ‘Product Details’ = SharePoint Online list
powerapps add column to collection

2. Next, insert the Data table control and set its Items property to the code:

Items = AddColumns(colProducts,TotalPrice,Price*Quantity)

Where,

  • TotalPrice = New column name
  • Price*Quantity = New column value
add columns to collection powerapps

3. To display the data table fields, click on the Edit fields option, choose the respective fields, and click on the Add button, as shown below.

power apps addcolumn

4. Once your app is ready, Save, publish, and Preview the app. The data table control displays a new collection column, as in the screenshot below.

power apps collection add column

Power Apps Add Column to Collection

Next, I will show you how to add a new column to the Power Apps collection using a simple example.

Example:

I have a Power Apps collection named “colEmployee” that has three fields/columns [EmployeeID, JoiningDate, and Name]. I used the code below to create it.

ClearCollect(
    ColEmployee,
    {
        EmployeeID: "SP001",
        Name: "Lynee",
        JoiningDate: Date(
            2020,
            5,
            15
        )
    },
    {
        EmployeeID: "SP002",
        Name: "Jane Smith",
        JoiningDate: Date(
            2019,
            8,
            20
        )
    },
    {
        EmployeeID: "SP003",
        Name: "Michael Johnson",
        JoiningDate: Date(
            2023,
            3,
            10
        )
    },
    {
        EmployeeID: "SP003",
        Name: "Johanna",
        JoiningDate: Date(
            2022,
            4,
            1
        )
    }
);

Where,

colEmployee = power Apps collection name

add new column to collection powerapps

Now, I would like to add a new column to calculate the years of service for each employee based on their “JoiningDate.” To do so, insert a Button control and set its OnSelect property to the code below.

OnSelect = ClearCollect(
    colEmployeeServiceYears,
    AddColumns(
        ColEmployee,
        YearsOfService,
        Year(Today()) - Year(JoiningDate)
    )
);

Where,

  • colEmployeeServiceYears = Power Apps new collection name
  • ColEmployee = Source collection
  • YearsOfService = New column name
add column to a collection powerapps

Finally, Preview the app. Once the user clicks on the button control, the new column is added to the Power Apps collection, as shown below.

See also  How to Set Default Selected Item in Power Apps Gallery?

Output:

add new column to collection power apps

This way, you can add a new column to the Power Apps collection.

Power Apps Add Multiple Columns to Collection

In the same way, I will show you how to add multiple columns to the Power Apps collection with a simple example.

Example:

I have created a Power Apps collection named “colOrders” that has three fields/columns [ProducID, Quantity, and UnitPrice]. I used the code below to create it.

ClearCollect(
    colOrders,
    {
        ProductID: "GS001",
        Quantity: 5,
        UnitPrice: 10
    },
    {
        ProductID: "GS002",
        Quantity: 3,
        UnitPrice: 15
    },
    {
        ProductID: "GS003",
        Quantity: 2,
        UnitPrice: 20
    },
    {
        ProductID: "GS004",
        Quantity: 7,
        UnitPrice: 19
    },
    {
        ProductID: "GS005",
        Quantity: 2,
        UnitPrice: 18
    },
    {
        ProductID: "GS006",
        Quantity: 9,
        UnitPrice: 25
    }
);

Where,

  • colOrders = power Apps collection name
Power Apps Add Multiple Columns to Collection

Now, I want to add two columns [DiscountedPrice and TotalPrice] using the existing collection. To do so, insert the Button control and set Its OnSelect property to the code below.

OnSelect = ClearCollect(
    colMultipleColumns,
    AddColumns(
        colOrders,
        TotalPrice,
        Quantity * UnitPrice
    )
);
ClearCollect(
    colAddMultipleColumns,
    AddColumns(
        colMultipleColumns,
        DiscountedPrice,
        If(
            Quantity > 3,
            TotalPrice * 0.9,
            TotalPrice
        )
    )
)

Where,

  • colAddMultipleColumns = Power Apps new collection
  • TotalPrice, DiscountedPrice = New collection columns
add column to collection

Once your updates are done, preview the app. When the user clicks on the button control, new columns will be added to the Power Apps collection.

This is how we can add multiple columns to the Power Apps collection.

Output:

power apps add column to existing collection

Power Apps Add Column to Existing Collection | Power Apps AddColumns LookUp

In this section, I will show you how to add a column to an existing collection or Power Apps AddColumns Lookup.

Example:

Input Collection1: [ColEmployee]

add new column to existing powerapps collection

Input Collection2: [ColDepatment]

Power Apps AddColumns LookUp

Now, I would like to add a Department column to the existing collection using the LookUp() function. To do so so, insert the Button control and set its OnSelect property to the code below.

OnSelect = ClearCollect(
    colLookup,
    AddColumns(
        ColEmployee,
        Department,
        LookUp(
            ColDepartments,
            EmployeeID = ColEmployee[@EmployeeID],
            Department
        )
    )
);
add column to existing collection powerapps

Finally, Preview the app. Once the user selects the button control, the lookup column is added to the Power Apps existing collection, as shown below.

See also  How to Create Dataverse File Field

Output Collection:

add column to existing collection in power apps

Power Apps AddColumns Sum

Next, we will discuss how to use a Sum function in the PowerApps AddColumns function using a simple scenario:

Scenario:

I have a Power Apps collection named “colSales,” and this collection contains three fields [TransactionID, Product, and SalesAmount]. I used the code below to create it.

ClearCollect(colSales,
    {TransactionID: "DC001", Product: "Laptop", SalesAmount: 100},
    {TransactionID: "DC002", Product: "Mobile", SalesAmount: 150},
    {TransactionID: "DC003", Product: "Watch", SalesAmount: 200}
);
power apps clearcollect addcolumns

Now, I would like to add a new column called “TotalSales” that represents the sum of all sales amounts in the collection. To achieve it, insert the Button control and set its OnSelect property as:

OnSelect = ClearCollect(colTotalSum,
    AddColumns(colSales,
        TotalSales,
        Sum(SalesAmount)
    )
);
power apps addcolumns sum

Once the user clicks on the button control, it represents the sum of all sales amounts in the collection.

Output:

add columns collection powerapps

Power Apps AddColumns Filter

I have a Power Apps collection named “colEmployeeDetails,” and this collection contains three fields [EmployeeID, Name, and Salary]. I used the code below to create it.

powerapps addcolumns filter

Now, I would like to add a new column called “SalaryCategory” based on the salary of each employee. Employees with a salary greater than $50,000 will be categorized as “High Income,” and others will be categorized as “Standard Income.”

To work around this, insert a Button control and set its OnSelect property to the code below.

OnSelect = ClearCollect(colFilter,
    AddColumns(colEmployeeDetails,
        SalaryCategory,
        If(Salary > 50000, "High Income", "Standard Income")
    )
);
power apps addcolumns filter

Once your app is ready, Save, publish, and Preview it. When the user selects the button control, a new column is added to the collection with filter records, as shown below.

Output:

how to use powerapps addcolumns filter

This way, you can work with the Power Apps AddColumns filter.

Moreover, you may like some more articles:

From this Power Apps tutorial, we learned the Power Apps add column to collection and Power Apps add multiple columns to collection.

Also, we covered the Power Apps add column to existing collection, powerapps addcolumns sum, and powerapps addcolumns filter.

  • >