Power Apps canvas apps: Adding Calculated and Dynamic Columns

Hello everyone and welcome to my blog. In today’s blog I will discuss on how you can add a column to a datasource in Power Apps canvas apps.

So let’s get started. To add a column to the datasource, you can use the AddColumn function to add a column to the datasource.

Let’s start with a simple example and gradually we will dive into more complex scenarios.

I want to set the Items property of the Gallery to Accounts datasource. Let’s say I need to have a CompositeAddress field which would be a combination of

• Address 1 Street 1
• City
• Pin Code

Using the below formula, I can add a dynamic column ‘CompositeControl‘ and add it as a column to my Accounts datasource.

AddColumns(
    Accounts,
    CompositeAddress,
    $"{ThisRecord.'Address 1: Street 1'}, {ThisRecord.'Address 1: City'}, {ThisRecord.'Address 1: ZIP/Postal Code'}"
)

I can now use the CompositeAddress field to bind it to any control in the Gallery.

Using the AddColumns function I can add more that one column and with values from related table.

In the below formula, I have added a new calculated column – ‘Account Credit Limit‘ with data from the Primary contact of the account which is a lookup on the Account table.

AddColumns(
    Accounts,
    CompositeAddress,
    $"{ThisRecord.'Address 1: Street 1'}, {ThisRecord.'Address 1: City'}, {ThisRecord.'Address 1: ZIP/Postal Code'}",
    'Account Credit Limit', LookUp(Contacts, ThisRecord.Contact = 'Primary Contact'.Contact).'Credit Limit'
)

Great isn’t it. But that’s not all. Not only you can define calculated columns with data from parent record, you can even add a dynamic column using the AddColumn function with data from child tables (1:N relationship).

In the below formula, I have added a column to get the count of email messages for each account.

AddColumns(
    Accounts As acc,
    AccountEmails,
    CountRows(
        Filter(
            'Email Messages',
            (AsType(
                Regarding,
                Accounts
            )).Account = acc.Account
        )
    )
)

Please note that in this case the count query is non-delegable. If your delegation limit is 500 and there are 501 email messages for an account, it would still return 500.

Hope this helped!
Debajit Dutta