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 Name	Data Type
ID	It is a default ID column
Product Name	It is a default single line of text column
Manufacturer	Choice
Price	Currency
Quantity	Number
Is Available	Yes/No

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.

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

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

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

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.

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

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

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.

Output:

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

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

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

Input Collection2: [ColDepatment]

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
        )
    )
);

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.

Output Collection:

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}
);

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)
    )
);

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

Output:

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.

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")
    )
);

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:

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

Moreover, you may like some more articles:

• Power Apps If Statement Examples
• Power Apps StartsWith and EndsWith Functions
• Power Apps GroupBy and Ungroup Functions
• Patch Function in Power Apps
• Power Apps Loading Spinner
• Pagination in Power Apps Gallery

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.