AddColumns, DropColumns, RenameColumns, and ShowColumns functions

Applies to: Canvas apps Desktop flows Model-driven apps Power Platform CLI

Shapes a table by adding, dropping, renaming, and selecting its columns.

The **ForAll function can also be used to shape a table, by returning a table of new records created from existing columns.

Overview

These functions shape a table by adjusting its columns:

• Reduce a table that contains multiple columns down to a single column for use with single-column functions, such as Lower or Sqrt.
• Add a calculated column to a table (for example, a Total Price column that shows the results of multiplying Quantity by Unit Price).
• Rename a column to something more meaningful, for display to users or for use in formulas.

A table is a value in Power Apps, just like a string or a number. You can specify a table as an argument in a formula, and functions can return a table as a result.

Note

The functions that this topic describes don't modify the original table. Instead, they take that table as an argument and return a new table with a transform applied. For more information, see working with tables.

You can't modify the columns of a data source by using these functions. You must modify the data at its source. You can add columns to a collection with the Collect function. For more information, see working with data sources.

Description

The AddColumns function adds a column to a table, and a formula defines the values in that column. Existing columns remain unmodified.

The formula is evaluated for each record of the table.

Fields of the record currently being processed are available within the formula. Use the ThisRecord operator or simply reference fields by name as you would any other value. The As operator can also be used to name the record being processed which can help make your formula easier to understand and make nested records accessible. For more information, see the examples below and working with record scope.

The DropColumns function excludes columns from a table. All other columns remain unmodified. DropColumns excludes columns, and ShowColumns includes columns.

Use the RenameColumns function to rename one or more columns of a table by providing at least one argument pair that specifies the name of a column that the table contains (the old name, which you want to replace) and the name of a column that the table doesn't contain (the new name, which you want to use). The old name must already exist in the table, and the new name must not exist. Each column name might appear only once in the argument list as either an old column name or a new column name. To rename a column to an existing column name, first drop the existing column with DropColumns, or rename the existing column out of the way by nesting one RenameColumns function within another.

The ShowColumns function includes columns of a table and drops all other columns. You can use ShowColumns to create a single-column table from a multi-column table. ShowColumns includes columns, and DropColumns excludes columns.

For all these functions, the result is a new table with the transform applied. The original table isn't modified. You can't modify an existing table with a formula. SharePoint, Microsoft Dataverse, SQL Server, and other data sources provide tools for modifying the columns of lists, tables, and tables, which are often referred to as the schema. The functions in this article only transform an input table, without modifying the original, into an output table for further use.

The arguments to these functions support delegation. For example, a Filter function used as an argument to pull in related records searches through all listings, even if the '[dbo].[AllListings]' data source contains a million rows:

AddColumns( RealEstateAgents,
   Listings,
   Filter(  '[dbo].[AllListings]', ListingAgentName = AgentName )
)

However, the output of these functions is subject to the non-delegation record limit. In this example, only 500 records are returned even if the RealEstateAgents data source has 501 or more records.

If you use AddColumns in this manner, Filter must make separate calls to the data source for each of those first records in RealEstateAgents, which causes lots of network chatter. If [dbo](.[AllListings] is small enough and doesn't change often. You can call the Collect function in OnStart to cache the data source in your app when it starts. As an alternative, you could restructure your app so that you pull in the related records only when the user asks for them.

Note

In Power Apps prior to version 3.24042, column names were specified with a text string using double quotes, and if connected to a data source they also needed to be logical names. For example, the logical name "cr43e_name" with double quotes was used instead of the display name Name without quotes. For SharePoint and Excel data sources that contain column names with spaces, each space was specified with "_x0020_", for example "Column Name" as "Column_x0020_Name". After this version, all apps were automatically updated to the new syntax described in this article.

Syntax

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

• Table - Required. Table to operate on.
• ColumnName(s) - Required. Names of the columns to add.
• Formula(s) - Required. Formulas 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.

DropColumns( Table, ColumnName1 [, ColumnName2, ... ] )

• Table - Required. Table to operate on.
• ColumnName(s) - Required. Names of the columns to drop.

RenameColumns( Table, OldColumnName1, NewColumnName1 [, OldColumnName2, NewColumnName2, ... ] )

• Table - Required. Table to operate on.
• OldColumnName(s) - Required. Names of the columns to rename from the original table. This element appears first in the argument pair (or first in each argument pair if the formula includes more than one pair).
• NewColumnName(s) - Required. Replacement names. This element appears last in the argument pair (or last in each argument pair if the formula includes more than one pair).

ShowColumns( Table, ColumnName1 [, ColumnName2, ... ] )

• Table - Required. Table to operate on.
• ColumnName(s) - Required. Names of the columns to include.

Examples

The examples in this section use the IceCreamSales data source, which contains the data in this table:

None of these examples modify the IceCreamSales data source. Each function transforms the value of the data source as a table and returns that value as the result.

Formula	Description	Result
AddColumns( IceCreamSales, Revenue, UnitPrice * QuantitySold )	Adds a Revenue column to the result. For each record, UnitPrice * QuantitySold is evaluated, and the result is placed in the new column.
DropColumns( IceCreamSales, UnitPrice )	Excludes the UnitPrice column from the result. Use this function to exclude columns, and use ShowColumns to include them.
ShowColumns( IceCreamSales, Flavor )	Includes only the Flavor column in the result. Use this function include columns, and use DropColumns to exclude them.
RenameColumns( IceCreamSales, UnitPrice, Price)	Renames the UnitPrice column in the result.
RenameColumns( IceCreamSales, UnitPrice, Price, QuantitySold, Number)	Renames the UnitPrice and QuantitySold columns in the result.
DropColumns( RenameColumns( AddColumns( IceCreamSales, Revenue, UnitPrice * QuantitySold ), UnitPrice, Price ), Quantity )	Performs the following table transforms in order, starting from the inside of the formula: Adds a Revenue column based on the per record calculation of UnitPrice * Quantity. Renames UnitPrice to Price. Excludes the Quantity column. Order is important, for example we can't calculate with UnitPrice after it's renamed.

Step by step

Let's try some of the examples from earlier in this article.

1. Create a collection by adding a Button control and setting its OnSelect property to this formula:

   ClearCollect( IceCreamSales,
      Table(
          { Flavor: "Strawberry", UnitPrice: 1.99, QuantitySold: 20 },
          { Flavor: "Chocolate", UnitPrice: 2.99, QuantitySold: 45 },
          { Flavor: "Vanilla", UnitPrice: 1.50, QuantitySold: 35 }
      )
   )
   

2. Run the formula by selecting the button while holding down the Alt key.

3. Add a second Button control, set its OnSelect property to this formula, and then run it:

   ClearCollect( FirstExample,
      AddColumns( IceCreamSales, Revenue, UnitPrice * QuantitySold )
   )
   

4. On the File menu, select Collections, and then select IceCreamSales to show that collection.

   As this graphic shows, the second formula didn't modify this collection. The AddColumns function used IceCreamSales as a read-only argument; the function didn't modify the table to which that argument refers.

   

5. Select FirstExample.

   As this graphic shows, the second formula returned a new table with the added column. The ClearCollect function captured the new table in the FirstExample collection, adding something to the original table as it flowed through the function without modifying the source:

   

Map columns in a component

See Map columns.