PowerApps AddColumns Function with Examples

In this PowerApps Tutorial, We will see what is PowerApps AddColumns function, What is its syntax, and how we can use it in PowerApps.

Also, by taking some simple scenarios, We will cover the below topics as:

• PowerApps addcolumns examples
• PowerApps addcolumns multiple columns
• PowerApps addcolumns to collection
• PowerApps addcolumns multiple
• PowerApps addcolumns not working
• PowerApps addcolumns filter
• PowerApps addcolumns sum
• PowerApps addcolumns concatenate

PowerApps AddColumns

• PowerApps AddColumns function helps to add a column to a table and the specified formula specifies the values in that column, whereas existing columns remain unmodified.
• In PowerApps, a table defines a value that is just like a string or a number. In the formula, We can specify the table as an argument and functions can return a table as a result.
• Simply we can say, it does not modify the original table. In this function, they take the table as an argument and return a new table.

Check out PowerApps Functions Tutorial and PowerApps First, FirstN, Last, and LastN functions with examples.

PowerApps AddColumns Function Syntax

Syntax:

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

Where,

1. Table = This is required that specifies the table to operate on.
2. ColumnName(s) = 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.
3. Formula(s) = 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.

PowerApps addcolumns examples

In this example, We will see how to use AddColumns function in PowerApps.

• Below represents the FruitSales data source that contains these below items in a table. It has three columns named “Fruit”, “Price” and “QuantitySold”.
• Here what I want to do is, I want another new column (i.e. Revenue) that will evaluate the Price and QuantitySold column and return that value as the result.

• In the below table, you can see what exactly I did and the result that has been stored in a new column as Revenue.

Formula	Description	Result
AddColumns( FruitSales, “Revenue”, Price * QuantitySold )	Adds a Revenue column to the result. For each record, UnitPrice * QuantitySold is evaluated, and the result is placed in the new column.

Read Power Apps List Box Control

PowerApps addcolumns multiple columns

Here we will discuss how to use PowerApps AddColumns function with multiple columns.

• The below screenshot represents a UserProfile table. This has only one column i.e. id/name/age and some rows like “1/Kamal/23”, “2/Preeti/25”, “3/Bijay/37” and so on.
• Now I am trying to convert this table to a new table that has three columns, “id”, “name”, and “age” or I can view these columns with a new data table in PowerApps.

• On the PowerApps screen, Insert a Data table and set its Items property to your table data source (UserProfile) as shown in the below screenshot.

• Now to split the columns individually, you can apply this below formula on Data table’s Items property as:

Items = AddColumns(
    UserProfile,
    "ID",
    First(
        Split(
            'id/name/age',
            "/"
        )
    ).Result,
    "Name",
    Last(
        FirstN(
            Split(
                'id/name/age',
                "/"
            ).Result,
            2
        )
    ).Result,
    "Age",
    Last(
        FirstN(
            Split(
                'id/name/age',
                "/"
            ).Result,
            3
        )
    ).Result
)

Where,

1. UserProfile = Data source table name
2. ‘id/name/age’ = This is the column from the table data source that you want to split

You can refer this below screenshot.

• Now Save and Preview (F5) the app. Once you will preview, you can view the table with individual columns (ID, Name, and Age) as shown in the above screenshot.

PowerApps addcolumns to collection

PowerApps AddColumns to Collection helps to add a column to a table or a collection and a formula defines the values in that column. Suppose you want to add a column to the PowerApps Collection, then follow the below example.

I have two PowerApps Collections named Product Description Collection and Product Price Collection.

1. The Product Description Collection is having two columns:

• ProductID
• ProductDescription

The below screenshot represents the formula reference:

2. Similarly, the Product Price Collection is having two columns:

• Productid
• ProductPrice

Refer to the below screenshot for the applied Powerapps Collection formula:

• Now what I want to do is, I want to create an additional Column in Collection1 (Product Description Collection) i.e. “ProductPrice” and bring in the Value from ProductPrice in Collection2 (Product Price Collection) based on the lookup of Collection2.Id=Collection1.ID
• For that, Insert a Button and set its Text property to “Product Description and Price“.
• Select the Button (Product Description and Price) and apply the below formula on its OnSelect property:

OnSelect = ClearCollect(
    ProductDescriptionAndPrice,
    AddColumns(
        ProductDescriptionCollection,
        "ProductPrice",
        LookUp(
            ProductPriceCollection,
            Productid = ProductDescriptionCollection[@ProductID],
            ProductPrice
        )
    )
)

Where,

1. ProductDescriptionAndPrice = This is the new collection name where you want to store the Product price value
2. ProductDescriptionCollection = Name of the Collection 1
3. “ProductPrice” = This is the new column that you added in the new collection (where the product price will store)
4. ProductPriceCollection = Name of the Collection 2
5. Productid = This is the id column from Collection 2 (ProductPriceCollection)
6. @ProductID = This is the ID column from Collection 1 (ProductDescriptionCollection)
7. ProductPrice = This is the column that present inside Collection 2 (ProductPriceCollection)

• Now Save and Preview the app. Click on these Buttons serially: Product Description Collection -> Product Price Collection -> Product Description and Price.
• Go to the PowerApps Collections page -> Select the new Collection name (Product Description and Price). Once you will select the collection, you can see the Product Price column has been added to the collection as shown below.

PowerApps addcolumns multiple

In this topic, We will see what the mean of PowerApps AddColumns Multiple, What is its use, and how we can use it. To know about details, refer to the below scenario.

• I have two Data sources:

1. Table 1 (UserTicketDetails):

This table has some different columns like {ID, Name, Date, Ticket Details}

2. Table 2 (UserRegion):

This table has these columns: {ID, Name, Region}

• In this scenario, I would like to be able to display items based on Region selected. I can use the Filter function on Table 1 by region but I would need to somehow join the 2 tables by common field [Name] in order to be able to do so.
• The below screenshot represents Table 1 i.e. UserTicketDetails. And it is having these below columns including some records.

• Similarly, the below screenshot represents Table 2 i.e. UserRegion. And it is having these below columns including the below records.

• Now as per the requirement, I have a Gallery control that will display the item details based on the Region. We can follow the below formula as:

gallery1.Items = AddColumns(Table1, "Region", LookUp(Table2, Name = Table1[@Name], Region))

• Based upon the above formula, I have selected the Gallery control and applied the formula on its Items property as:

Items = AddColumns(
    UserTicketDetails,
    "Region",
    LookUp(
        UserRegion,
        Name = UserTicketDetails[@Name],
        Region
    )
)

• Now select the Gallery control -> Go to the Properties pane -> Click on Edit from the Fields section -> Select Name as Title and Region as Subtitle as shown in the below screenshot.
• Save and Preview (F5) the app. You can see the gallery control with Name and Region as below.

PowerApps addcolumns not working

Here we will see an example that how you can use PowerApps AddColumns using the UpdateIf function.

• AddColumns doesn’t change the collection that is passed to it – instead, it returns a new collection with the newly added column(s).
• In this example, What I need to do is, I have the extra column i.e. creating in my local collection, and then I will use the UpdateIf function to set the values in that column.
• Now I will create a new PowerApps collection i.e. Info. In that collection, I will create some columns like Id, Name, Age, and Human. Also, I want to add a new column named Species and the value will be blank in each record of that specific collection.
• The formula will be applied on the Button’s OnSelect property as:

OnSelect = ClearCollect(
    Info,
    AddColumns(
        Table(
            {
                Id: 1,
                Name: "Prateek",
                Age: 7,
                Human: false
            },
            {
                Id: 2,
                Name: "Chinmay",
                Age: 17,
                Human: true
            },
            {
                Id: 3,
                Name: "Remya",
                Age: 17,
                Human: true
            },
            {
                Id: 4,
                Name: "Sidharth",
                Age: 16,
                Human: true
            },
            {
                Id: 5,
                Name: "Shradhha",
                Age: 15,
                Human: true
            },
            {
                Id: 6,
                Name: "Roopak",
                Age: 3,
                Human: false
            }
        ),
        "Species",
        ""
    )
);
UpdateIf(
    Info,
    true,
    {Species: Blank()}
)

Refer to the below screenshot.

• Now Save and Preview (F5) the app. Click on the Button (Hit me) and then go to that specific collection (Info), you can see the specific columns (including Species) with all records as like the below screenshot.
• Here, the Species column value will be blank in each record as shown below.

• Now again insert a new Button and rename it to “Click me” and apply this below formula on its OnSelect property as:

OnSelect = UpdateIf(
    Info,
    Human = true,
    {Species: "Homo sapiens"}
)

• In this above code, it specifies if the Human value is “true“, then it will update the Species value as “Homo sapiens“.
• Again Save and Preview (F5) the app and then click on the button (Click me).

• Go to that specific collection (Info), you can see the Species value will update with Homo sapiens whose Human value is true as in the below screenshot.

PowerApps addcolumns filter

Suppose you want to use the PowerApps AddColumns and the filter function at a time in the data tables. Let’s overlook at the below formula.

• If you want to filter the Data table items within your app, then apply the below formula on the Data table’s Items property:

Items = AddColumns(
   Filter(ModuleAssessments,Title=VarModCode), /* <-- Modify here */
   "Weighting%",Weighting&"%",
   "Handout date",Text(Date_x0020_of_x0020_Handout,"[$-en-US]dd mmm yyyy"),
   "Submission date",Text(Date_x0020_of_x0020_Submission,"[$-en-US]dd mmm yyyy"),
   "Feedback date",Text(Date_x0020_of_x0020_Feedback_x00,"[$-en-US]dd mmm yyyy")
)

Or

Items = Filter(
  AddColumns(
    ModuleAssessments,
   "Weighting%",Weighting&"%",
   "Handout date",Text(Date_x0020_of_x0020_Handout,"[$-en-US]dd mmm yyyy"),
   "Submission date",Text(Date_x0020_of_x0020_Submission,"[$-en-US]dd mmm yyyy"),
   "Feedback date",Text(Date_x0020_of_x0020_Feedback_x00,"[$-en-US]dd mmm yyyy")
   ),
  Title=VarModCode
)

• If you want to know more details about the PowerApps Gallery, follow the below link:

PowerApps Gallery Filter

• Also, you can refer to the below link to know how to create a PowerApps Date Filter:

Create PowerApps Date filter

PowerApps addcolumns sum

In the below scenario, We will discuss how to use a Sum function in PowerApps AddColumns function. Let’s take a simple example.

• I have a SharePoint list named Classes. This list has these four different columns:

1. Title = This is the default column of the SharePoint list
2. Subject ID = This is the single line of text column
3. Academic Hours = This is the Number data type column that specifies the academic hours
4. Actual Hours = This is also a Number data type column that specifies the actual hours

• Here, I need a table that aggregates all the classes to see something like the following screenshot.

• So to do this, I will use the PowerApps GroupBy function, CountRows function, and as well as Sum function. You can add multiple columns at once in a single AddColumns call.
• On the PowerApps screen, Insert a Data table and set its Items property to the below formula:

Items = AddColumns(
    GroupBy(
        Classes,
        "SubjectID",
        "All Subjects"
    ),
    "Row Count",
    CountRows('All Subjects'),
    "Sum Aacademic Hours",
    Sum(
        'All Subjects',
        'Academic Hours'
    ),
    "Sum Actual Hours",
    Sum(
        'All Subjects',
        'Actual Hours'
    )
)

Where,

1. Classes = SharePoint list name
2. “Sum Academic Hours”, “Sum Actual Hours” = These are the table headers that will display in the Powerapps Data table
3. “SubjectID”, ‘Academic Hours’, ‘Actual Hours’ = These are the SharePoint Column names

Refer to the below screenshot:

• Now, what we can do is, Select the Data table -> Go to Properties pane -> Click on Edit fields from Fields section -> + Add field -> Select Subject ID, Sum Academic Hours, and Sum Actual Hours as shown below.
• Now Save and Preview (F5) the app. You can see the total sum of the Academic Hours as well as the Actual Hours in the below screenshot.

Also if you are interested to learn more about the PowerApps Sum function, then refer to the below link:

• PowerApps Sum Function

PowerApps addcolumns concatenate

In this PowerApps AddColumns Concatenate, We will see how to work with the Concat function in the PowerApps AddColumns function.

• In this example, I need to concatenate or join the values of a column from a table to generate a single string of text.
• The below screenshot represents a PowerApps Collection named Animals that is having these below values as:

1. Tiger
2. Lion
3. Elephant

• Now, I would like to concatenate those values and use a comma as a delimiter to create a text value of Tiger, Lion, and Elephant.

• To do that, Insert a Label control and set its Text property to the below formula:

Text = Concat(Animals, Name & ", ")

Where,

1. Animals = PowerApps Collection Name
2. Name = Collection Header

• Save and Preview the app. You can see the result in the PowerApps Label control (with delimiter) as in the below screenshot.

Also, you may like the below PowerApps Tutorials:

• PowerApps Examples – COVID 19 Self Declaration form
• PowerApps Patch Function with examples
• PowerApps ForAll Function with examples
• PowerApps if statement with examples
• PowerApps Replace Function with examples
• PowerApps Employee Directory
• PowerApps Pen input control (PowerApps signature)
• PowerApps SharePoint Lookup Column + PowerApps Dropdown Example
• How to add PowerApps app to Microsoft Teams – Step by Step
• How to build multilingual app in PowerApps
• PowerApps Barcode Scanner – Detailed tutorial
• How to Create CSV in SharePoint using PowerApps and Power Automate
• PowerApps Combobox Control

In this PowerApps Tutorial, We saw what is PowerApps AddColumns function, What is its syntax, and how to use AddColumns in the PowerApps function.

Also, by taking some simple scenarios, We covered the below topics as:

• PowerApps addcolumns examples
• PowerApps addcolumns multiple columns
• PowerApps addcolumns to collection
• PowerApps addcolumns multiple
• PowerApps addcolumns not working
• PowerApps addcolumns filter
• PowerApps addcolumns sum
• PowerApps addcolumns concatenate