AddColumns by Power Apps is one of the most used functions in the Microsoft low-code development environment. This functionality allows users to add new columns to their tables, enriching them with additional information and simplifying data entry. In this article, we'll dive into what Power Apps' AddColumns feature is and how to use the AddColumns function. We'll show you some examples of insertion, the characteristics that distinguish it from similar functions, and a simple use case within an application to manage your company's inventory.
The function AddColumns, available in Power Apps of Microsoft Power Platform, is one of the fundamental functions for manipulating and managing data in an application developed within Microsoft's low-code development environment and plays a crucial role in helping users organize and add new data within our apps.
AddColumns allows you to add new columns to your data tables with calculations based on existing values, enrich the data by adding additional information from other sources or calculated, and prepare them by adding columns that simplify their visualization and analysis, making it much easier to add and process new information in your apps.
Using this feature for our Power Apps applications provides users with a more intuitive and user-friendly approach to adding and viewing the latest information. AddColumns makes it possible to provide users of our apps with more complete, personalized and easily understandable data by anyone, significantly reducing efforts related to adding and integrating new information.
As already mentioned above, the AddColumns function in Microsoft Power Apps works by adding new columns to an existing table and calculating the values of these new columns based on formulas specified by the user.
To use it, first we'll need to specify the table to which we want to add the columns. Next, we'll have to define the new columns that we want to add to the table.
For each new column, you will need to specify a name for the column and a formula to calculate its value. This formula can be a simple expression that is based on other columns in the same row, or it can be a more complex formula that uses aggregation functions or conditional calculations.
Once the new columns and their formulas have been defined, Power Apps will evaluate these formulas for each row in the existing table. For each row, it will calculate the values for the new columns based on the formulas we have specified.
Finally, after evaluating the formulas, the calculated values will be added as new columns to the existing table. Now the table we were working on will contain the original columns plus the new columns just calculated.
The syntax of the AddColumns function in Microsoft Power Apps should look something like this in the function box in the Power Apps Studio user interface:
addColumns (DataSource, ColumnName1, Formule1 [, ColumnName2, Formula2,...])
Now let's take a closer look at the parameters that make up this function to understand what they do:
Within the function you can add multiple pairs of column names and formulas (ColumnName2, Formula2, etc.) in order to be able to add more columns at the same time.
To better understand how you can implement it within your applications, we will now consider some more specific examples of how to use the AddColumns function in Microsoft Power Apps.
Suppose we have to modify a table called Employee With the columns Name, Department and Salary. To this table we want to add a new column called Bonus which represents the monthly bonus for each employee. To do this, we can use AddColumns like this:
ClearCollect (EmployeeWithBonus, AddColumns (Employee, “Bonus”, Salary * 0.1))
With this function we will create a new collection called EmployeeWithBonus which contains all the data in the table Employee, but with a new column Bonus calculated by multiplying the items in the Salary column by 0.1 (i.e. 10% of the salary).
If, on the other hand, we want to add more than one column at the same time, we can do so by including multiple pairs of column name and formula within our AddColumns function, as in the example below:
ClearCollect (EmployeeWithDetails, AddColumns (Employee, “FullName”, Name & "(” & Department & “)”, “Bonus”, Salary * 0.1))
In addition to the column Bonus as in the previous example, we are also adding a new column of name FullName that combines the name and department of each employee.
We can also use AddColumns to add columns to an existing collection. As an example, let's say we have a collection called Orders and we want to add a new column called TotalPrice, calculated by multiplying the quantity by the price of each order. The syntax of the function used will be this:
ClearCollect (OrdersWithTotalPrice, AddColumns (Orders, “TotalPrice”, Quantity * Price))
In this case, we are creating a new collection called Orders with Total Price which includes all the data in the collection Orders, but with a new column TotalPrice, calculated for each line.
The Addcolumns function can (and is often) used in combination with other functions available in Microsoft Power Apps.
It is very common to use AddColumns together with functions such as Filter, Search, Sum, Concatenate and many others to modify and manipulate data in a more specific way.
You can concatenate various data transformation operations into a single expression to obtain the desired result. Let's take a look at some of the most common examples of this practice with some of the most used functions.
We can use the AddColumns function together with the function Filter to add new columns to the filtered data.
For example, suppose we have a table called EmployeeData With the columns Name, Department and Salary. We want to filter only the employees who work in the 'Sales' department and add a new column to them called Bonus, calculated based on salary:
AddColumns (Filter (EmployeeData, Department = 'Sales'), 'Bonus', Salary * 0.1)
Now let's look at the parameters to understand what they do:
In this way, we obtain a new table with the original columns plus the additional Bonus column for employees of the 'Sales' department.
The AddColumns function in Microsoft Power Apps can be used with lookup fields to add new columns to the resulting table. For example, we could have a table Orders which contains a lookup field called CustomerID, which refers to the table Customers.
We can use AddColumns to add a new column called CustomerName To the table Orders, which shows the customer's name corresponding to CustomerID:
AddColumns (Orders, “CustomerName”, LookUp (Customers, ID = Orders.CustomerID, Name))
The parameters are:
This expression will add a new column called CustomerName To the table Orders, which will show the customer's name corresponding to each CustomerID.
If we want to use the AddColumns function to calculate the sum of values in an existing column and add the result as a new column, we can do it using the function Sum inside AddColumns. Suppose we have a table called Sales with a column named Amount which contains the sales amounts.
We can use AddColumns to add a new column called TotalAmount which contains the sum of the sales amounts for each row of the table. The syntax we could use would be this:
AddColumns (Sales, “TotalAmount”, Sum (Sales, Amount))
The parameters in this case are:
This expression will add a new column called TotalAmount To the table Sales, which will contain the sum of the sales amounts for each row of the table.
If we want to use the AddColumns function to concatenate the values of two existing columns and add the result as a new column, we can do it using the function Concatenate within AddColumns. Let's say we have a table called Employees with two columns: FirstName and LastName.
We can use AddColumns to add a new column called FullName which contains the concatenation of the values of FirstName and LastName for each row of the table:
addColumns (Employees, “fullName”, Concatenate (Employees.firstName, "“, employees.lastName))
As always, let's take a look at the parameters that make up the function:
This expression will add a new column called fullName to the Employees table, which will contain the concatenation of the FirstName and LastName values for each row in the table.
Dev4Side Software specializes in creating business apps with Power Apps, drastically reducing internal application development and maintenance processes.
Operating vertically across the entire Microsoft Power Platform ecosystem, we have developed extensive expertise in Power Apps, which allows us to offer custom-designed and fully integrated solutions within your Microsoft 365 tenant.
Contact us to transform business information into concrete actions.
Delegating a function in Power Apps of Microsoft Power Platform means allowing the underlying data server to manage the operation instead of doing it on the client side, a particularly important decision when working with large data sets, because it allows you to improve the performance and scalability of the application on the user side.
In a nutshell, when a function can be delegated, it is executed on the server instead of on the user's device, ensuring greater efficiency in data processing.
The AddColumns function can be delegated to Microsoft Power Apps. However, there are some limitations that you should consider when using AddColumns with remote data sources, such as SharePoint or SQL databases.
First, delegating data transformation functions such as AddColumns depends on the capabilities of the remote data source system. Some complex operations may not be delegable and may require full local data upload for processing.
The delegation of AddColumns could also be negatively affected by the amount of data in the table. If the table contains a large number of rows, remote processing delays may occur or delegation restrictions may apply.
Finally, not all functions used within Addcolumns may be delegable. It is therefore important to always consult the specific documentation of the chosen data source system to understand which functions are supported and the related limitations on delegation possibilities.
When using AddColumns with remote data sources, it is therefore advisable to test performance and consider the specific delegation limitations of the data source system to ensure the optimal functioning of your app and not run into errors or malfunctions that may compromise the experience of our users.
Although AddColumns is a very simple function to understand and apply for those who are just getting closer to the world of developing Power Apps, we can run into rather bizarre and confusing situations on the net in which novice users may wonder what is the point of using AddColumns and some of its more advanced formulas when it would be possible to apply other functions and obtain similar results.
So let's clear up the confusion on the subject and understand the main difference between AddColumns and other commands such as updateIF and Patch, that is, the way in which they operate on the data within a table in Microsoft Power Apps.
The updateIf function updates the values of existing columns in a table based on conditions specified by the user and can be useful when we want to change the values of existing columns based on certain conditions without adding new columns.
The Patch function allows you to add, modify or delete records in a table, and can also be used to update specific column values of an existing record and is useful when you want to directly modify the data in a table instead of adding new columns.
AddColumns, on the other hand, as we have already seen, adds new columns to an existing table, calculating the column values based on formulas specified by the user, enriching the data with the new calculated information.
In addition to the AddColumns function, there are other commands dedicated to modifying, adding and removing columns in Microsoft Power Apps that we might run into and that could be useful in managing our data.
So let's take a look and a brief description of what are the most common functions that we may encounter when we want to work with the columns of our data tables:
Now that we have all the necessary information to understand how the AddColumns function works, let's see its possible inclusion in a real development scenario of a possible business app.
Let's imagine that we are developing a management app for our inventory and that we need to have to view the data contained in an Excel sheet and be able to add others directly from our app. Let's see how to do it.
First, we'll focus on retrieving data from the inventory to populate our application.
Using an appropriate connector to access the inventory data on the Excel sheet called 'Product Inventory' and we use the ClearCollect function to upload the inventory data to a local collection within the app.
ClearCollect is a function that cleans the existing collection (if any) and then populates the collection with new data from the specified data source. The syntax that we will use will be this:
Clear Collect (Inventory Collection, Product Inventory)
With this formulation, we will retrieve the data from the Excel sheet “InventoryProducts” and we will upload them to the local collection called “CollectionInventory”.
Once this step is completed, we will have the inventory data immediately available within our Power Apps app, ready for further processing.
Now, suppose we want to add an 'Availability' column to our inventory data, which indicates if a particular product is currently available in our warehouses. To do this we will use the AddColumns function with the following syntax:
ClearCollect (Inventory Collection with Availability, AddColumns (Inventory Collection, 'Availability', If (Quantity > 0, 'Available', 'Unavailable')))
Now we will have a new collection called 'Inventory Collection with Availability' which includes the inventory data enriched with the additional column 'Availability'. This will allow us to easily view the availability status of the products within our Power App.
In this case, the value of the 'Availability' column will be calculated based on the available quantity of the product. If the quantity is greater than zero, the product will be considered 'Available'; otherwise, it will be considered by the app as 'Unavailable'.
Now let's see how to implement the logic for adding additional columns to the inventory to enrich it with calculated information. Let's imagine that what we are interested in is adding a 'Profit' column to our inventory data to calculate the gross profit of each product.
To do this, we will always use the Addcolumns function, with the following syntax:
ClearCollect (Profit Inventory, AddColumns (Inventory, “Profit”, Quantity Sold * (Sales Price - Product Cost))
Now we will have a new collection called 'Inventory with Profit' which includes the inventory data enriched with the additional column 'Profit'. The value of the 'Profit' column will be calculated by multiplying the quantity sold of the product by the difference between the sales price and the actual cost of the product.
After using the AddColumns function to enrich inventory data with additional or calculated information, it will be important to give our users the opportunity to view this data in our app in a clear and accessible way.
In this case, we're going to connect a gallery to the new “Inventory with Profit” data set and view additional information, such as gross profit, along with other inventory information.
Later, you can customize the gallery layout to display the additional columns clearly and intuitively. For example, you can add additional fields such as' Profit 'such as labels or values displayed within each item in the gallery.
Finally, once the desired result has been obtained, make sure to test our app rigorously to ensure that there are no errors or malfunctions.
We pay attention to the data visualization that is intuitive and meets the needs of the users who will use the app by making the necessary changes to the layout or formatting to improve the user experience of our application. Once finished, the app will be ready for distribution to our users.
Finally, as we have been able to observe, using the AddColumns function in Microsoft Power Apps allows us to enrich our data by adding new columns with additional or calculated information without having to modify our main data source and perform complex or customized data processing operations directly within Power Apps, without having to resort to external tools.
By applying formulas and criteria specific to our needs, we can manipulate our data in a simple and intuitive way, making it infinitely easier to add and process new information in our business apps and halving the efforts related to entering and integrating new data into them.
The Power Apps AddColumns function allows users to add new columns to a data table. It helps in enriching existing data with additional or calculated information, making data manipulation and visualization easier within the app.
The Power Apps AddColumns function works by adding new columns to a data source, where the values for these columns are calculated based on specified formulas. Users define the table, column names, and formulas to add the data.
The syntax for Power Apps AddColumns is:AddColumns (DataSource, ColumnName1, Formula1 [, ColumnName2, Formula2,...]),
where DataSource is the table, and ColumnName and Formula represent the new columns and their values.
Yes, Power Apps AddColumns can be combined with functions like Filter, LookUp, and Sum to perform advanced data manipulations, enabling users to filter, aggregate, or lookup related data while adding new columns.
You can add multiple columns in Power Apps AddColumns by specifying additional pairs of ColumnName and Formula within the function:AddColumns (DataSource, "Column1", Formula1, "Column2", Formula2).
The Power Apps AddColumns function supports delegation but with limitations. Some complex operations might not be delegable depending on the data source, so testing performance and consulting documentation is essential when working with large datasets.
Examples include adding a Bonus column to an employee table or a TotalPrice column to an orders table, calculated by applying formulas to existing columns.
While AddColumns creates new columns with calculated values, UpdateIf modifies existing columns based on conditions without adding new ones.
Similar functions include RemoveColumns (to delete columns), RenameColumns (to rename), and SelectColumns (to select specific columns), all used to modify tables in Power Apps.
In business applications like inventory management, Power Apps AddColumns can be used to add new columns like Availability or Profit, enabling businesses to track stock status or financial performance directly in the app.
The Modern Work team effectively and swiftly addresses IT needs, primarily focusing on software development. The technical staff is well-trained in implementing software projects using Microsoft technology stacks and is skilled in managing both agile and long-term projects.
