As Power Apps developers, we frequently encounter situations where we need to manipulate and reshape data dynamically to meet specific app requirements. One powerful tool for this task is the AddColumns function. Whether you're working with SharePoint, Dataverse, or SQL data sources, understanding how to use AddColumns effectively can make your app development process smoother and more efficient.
Why do we need tools like AddColumns? There are lots of reasons but the most common two are:
One, we don't own the data source. Meaning we can't change the columns or what is avaialable we have to "make due" with what they provide. So adding our own to facilate functionality is a must.
Two, we want to do something advanced. Creating a dynamic list of the next 10 Mondays in a dropdown, building an editable grid, or some other complex scenario where you need to be nimble. AddColumns lets you do all of this and more.
In this post, we’ll explore practical use cases of the AddColumns function, along with other helpful data manipulation tips. So, grab your Power Apps toolkit, and let’s dive in!
BTW - If all of this reading isn't your jam and you prefer video, I have got your covered AddColumns in Power Apps. We do all of this and then some.
Handling Delegation and Performance
Before you dive into the functions, one of the critical things to understand when using AddColumns or any of the data shaping formulas is delegation.
Delegation refers to how much data Power Apps can handle and process on the backend server rather than on the client device. Some functions in Power Apps, including AddColumns, are non-delegable, which means that they can only work on the first 500 (or another limit set by your environment) records of your data.
If you're working with large datasets, you must be cautious when applying AddColumns, as it might not return all rows, only the ones within the delegation limit. To prevent this, be mindful of where and how you use the function.
To better understand delegation, check out our comprehensive guide on Power Apps delegation.
Common things to keep in mind
All of these functions are used to generate a table with a new shape. As you will see in the examples you can change a data source table or a table you generate. This generated table doesn't affect the table that is being shaped, meaning if you use AddColumns on a SharePoint list it isn't changing the SharePoint list.
These functions are most often used in the Items property of a gallery or dropdown. Or if you need it more available then you can capture the output in a collection.
What Is AddColumns and Why Should You Use It?
At its core, AddColumns allows you to create a new column in your existing table, based on a calculation or formula, without altering your original data source. This is crucial because in many cases, you’re not allowed (or don’t want) to modify your backend data, especially if you’re dealing with a complex or restricted system like SharePoint or SQL.
For instance, imagine you have an employees list in SharePoint with separate columns for First Name and Last Name, but you want to display a Full Name in your app. Instead of modifying the data source to combine these fields, AddColumns allows you to do this directly in Power Apps.
Example 1: Creating a Full Name Column
Let’s start with a basic example. You want to display the full name of an employee in your Power App. Here’s the formula:
AddColumns(Employees, FullName, FirstName & " " & LastName)With this simple line, we’ve created a new column, Full Name, that combines the first and last names without touching the data in the original SharePoint list.
Example 2: Sorting by Complex Columns
Sorting data in Power Apps is generally straightforward until you deal with complex columns, such as Choice fields.
Let’s say you have a Favorite Color choice column in your SharePoint list, and you want to sort your employees by their favorite colors. Sorting a complex column directly might throw errors. Here’s where AddColumns comes to the rescue:
AddColumns(Employees, FavoriteColorValue, FavoriteColor.Value)Now that you’ve created a new text column for FavoriteColorValue, you can sort by it without issue:
SortByColumns(AddColumns(Employees, FavoriteColorValue, FavoriteColor.Value),"FavoriteColorValue")
Example 3: Grouping Data and Using AddColumns for Summarization
Another exciting use case for AddColumns is when you need to group your data or calculate summaries. For example, you may have a list of sales transactions, and you want to group the data by department while calculating the total sales for each.
Using AddColumns along with GroupBy and Sum functions, you can achieve this seamlessly:
AddColumns( GroupBy(Sales, "Department", "GroupedSales"), TotalSales, Sum(GroupedSales, SalesAmount))This formula adds a new column, TotalSales, to your grouped data, summarizing the sales amount for each department.
Note: The screenshot has HourlyWage in place of SalesAmount. My demo data didn't line up exactly with the point I wanted to make.
Example 4: Creating Dynamic Date Ranges Using AddColumns
Here’s a quick trick for generating dynamic date ranges. Say you want to create a drop-down list of the next 10 same day of the week, starting from today. AddColumns works perfectly with Sequence, a function that generates a sequence of numbers that we can turn into dates.
AddColumns( Sequence(10, 0, 7), SameDay, DateAdd(Today(), Value, TimeUnit.Days))
RenameColumns: Adjusting Column Names for Consistency
When working with external data sources or collaborating with multiple teams, you may find that column names are not always consistent or easy to work with. That’s where RenameColumns comes in handy. This function allows you to rename one or more columns in your dataset without altering the actual data source.
For example, if you’re working with a dataset that uses the column named Department but prefer to display it as Dept in your app, you can quickly rename it using this function:
RenameColumns(Employees, Department, Dept)This simple rename action can make your app easier to maintain, especially when aligning with standardized naming conventions across your organization.
DropColumns: Clean Up Unnecessary Data
Sometimes, you’re working with a dataset that contains more information than you need. Excess data can slow down your app and make your formulas more difficult to manage.
That’s where DropColumns can help. This function allows you to remove unwanted columns from a table without affecting the data source.
For example, let’s say you have an employees table with a Photo column, but the photo is not needed for a particular view in your app. You can drop that column like this:
DropColumns(Employees, Photo)By using DropColumns, you streamline your app's performance and ensure that only relevant data is downloaded to your app.
ShowColumns: Focus Only on What’s Needed
While DropColumns removes certain columns from a table, ShowColumns works in the opposite direction: it helps you specify exactly which columns you want to have available. This can be particularly useful when you’re dealing with large datasets but only need a few specific pieces of information.
For example, if you only want to display an employee’s First Name, Last Name, and Department, you can use ShowColumns to limit the data returned:
ShowColumns(Employees, FirstName, LastName, Department)By focusing only on the necessary data, you enhance both performance and readability in your app, ensuring that users see exactly what they need—nothing more, nothing less.
Wrapping Up
The AddColumns function is an essential tool for shaping and manipulating data on the fly in Power Apps, allowing you to do some really cool stuff. But don't forget about the functions friends. ShowColumns, DropColumns, and RenameColumns all have their magic moment where they are the perfect answer. And the more Power FX you know, the better.
If you need help with this, or anything Power Platform related Click the Contact Us button at the top of the page and we would be happy to help.
Comments