PowerApps: Add/Drop/Show/Rename Column

Hello Friends,

Welcome back with some new topics. Today, we will discuss about Column operations in PowerApps. PowerApps provides below options to play on columns-

1. AddColumns
2. ShowColumns
3. DropColumns
4. RenameColumns

These functions are used to re-shape the collection/table in PowerApps. Apart from that, it also provide functionality to get distinct data using Distinct function.

In this post, we will learn about:

1. Add/Show/Drop/Rename Column functions
2. Distinct function
3. Adding Blank Record (or "Please Select") at Beginning/End of collection (beneficial when binding with Dropdown)
   
4. Add ID using Filter function in collection having Distinct data

Let's start one by one. 

1. First we will create a collection. I had created a collection named "collSampleData". The data is:

2. ClearCollect(
       collSampleData,
       {
           ID: 1,
           FName: "Sachin",
           LName: "Jain"
       },
       {
           ID: 2,
           FName: "Lokesh",
           LName: "Kumar"
       },
       {
           ID: 3,
           FName: "Rajeev",
           LName: "Khanna"
       },
       {
           ID: 4,
           FName: "Anuj",
           LName: "Sharma"
       },
       {
           ID: 5,
           FName: "Rupesh",
           LName: "Jaiswal"
       },
       {
           ID: 6,
           FName: "Anuj",
           LName: "Sharma"
       },
       {
           ID: 7,
           FName: "Sachin",
           LName: "Jain"
       },
       {
           ID: 8,
           FName: "Sanjiv",
           LName: "Sharma"
       },
       {
           ID: 9,
           FName: "Rajeev",
           LName: "Sharma"
       }
   );  
   

3. It will create a collection with 9 records. Here I had added couple of duplicate entries which we will use for Distinct function.
4. 
5. If we check the collection, it has:
6. 
   
   
   
   
7. Add Column (AddColumns):
1. AddColumns function is used to add column to the collection/table. You may add a calculated column, add and existing column with different datatype. I am using to add a calculated column by adding FName & LName.

2. ClearCollect(
       collWithFullName,
       AddColumns(
           collSampleData,
           "FullName",
           FName & " " & LName
       )
   );
   

3. 
   
   
4. If we check the collection, it has:
   
5. 
   
   
6. This way, we can add columns to collection.
   
8. Show Column (ShowColumns)
1.  ShowColumns is used to include only those columns in table/collection that needs to be displayed and rest needs to be dropped.

2. ClearCollect(
       collShowColumnData,
       ShowColumns(
           collWithFullName,
           "ID",
           "FullName"
       )
   );
   

3. 
4. If we check the collection, it has:
5. 
   
6. Clearly, we can see, only 2 columns are retained and rest are removed. This is useful when we need to show a couple of columns from a large columns list.
9. Drop Column (DropColumns)
1.  DropColumns is used to drop the columns from table/collection that needs not to be displayed. It can be say a opposite of ShowColumns.

2. ClearCollect(
       collWithOnlyFullName,
       DropColumns(
           collWithFullName,
           "ID",
           "FName",
           "LName"
       )
   );
   

3. 
   
4. If we check the collection, it has:
5.  
   
   
6. Clearly, we can see, all 3 columns are dropped and rest only 1 column is retained. This is useful when we need to drop couple of columns from a large columns list and rest to be retained.
10. Distinct (Distinct)
1. Before moving forward to RenameColumns, we will discuss about Distinct function. After this, you will better understand the RenameColumns function. Distinct function is used to get the distinct values from a table/collection. Distinct works only on single column and return that particular column from the collection.
   

2. ClearCollect(
       collDistinctData,
       Distinct(
           collWithOnlyFullName,
           FullName
       )
   );
   

3. 
   
   
   
4. If we check the collection, it has:
5. 
   
11.  Rename Column (RenameColumns)
1. RenameColumns is used to rename the name of existing column.

2. ClearCollect(
       collRenameColumnData,
       RenameColumns(
           collDistinctData,
           "Result",
           "Full Name"
       )
   );
   

3. 
   
   
   
4. If we check the collection, it has:
5. 
   
   
   
6. As we can check the column name has been renamed from "Result" to "Full Name" while the data remains unchanged.
   
12. Nested Use Of These Functions
    
13. Now, once you are expertise in using functions, you may consolidate all these functions into one. I am using below function in one command to achieve the same functionality-
1. AddColumns
2. DropColumns
3. RenameColumns
4. Distinct

14. ClearCollect(
        collConsolidated,
        RenameColumns(
            Distinct(
                DropColumns(
                    AddColumns(
                        collSampleData,
                        "FullName",
                        FName & " " & LName
                    ),
                    "FName",
                    "LName"
                ),
                FullName
            ),
            "Result",
            "Full Name"
        )
    );
    

15. 
    
    
    
16. If we check the collection, it has:
17. 
    
    
    
18. Add Blank Record At Beginning/End of Collection
19. We have consolidated data collection. We will add a blank record at the end of collection.
    

20. ClearCollect(
        collBlankAtEnd,
        collConsolidated,
        {'Full Name': Blank()}
    );
    

21. 
    
    
22. For the purpose to display blank record, I had set border of labels used.
    
23. Now, we will add a blank record at the beginning of collection.

24. ClearCollect(
        collBlankAtBegin,
        {'Full Name': Blank()},
        collConsolidated
    );  
    

25. 
    
    
26. Add ID using Filter function in collection having Distinct data
27. Now suppose a scenario, where you have a collection which is having multiple duplicate records. Your objective is to have Distinct records having ID and Name. The problem is that Distinct function returns only single column, so what the approach is?
    
1. We will create a collection.
2. Apply Distinct (to get distinct records) >> AddColumns (add ItemID column with default value as 0) >> RenameColumns (rename the column "Result" created as as result of Distinct function to ItemName).
3. Create a replica of this distinct collection to apply ForAll loop.
4. Apply ForAll loop. In this loop apply Patch. In Patch, use Filter with First function to get the desired ItemID of each item.
28. Sequentially code is as below-

1. ClearCollect(
       collStationeryData,
       {
           ID: 1,
           ItemName: "Pencil"
       },
       {
           ID: 2,
           ItemName: "Pen"
       },
       {
           ID: 3,
           ItemName: "Eraser"
       },
       {
           ID: 2,
           ItemName: "Pen"
       },
       {
           ID: 4,
           ItemName: "Sharpner"
       },
       {
           ID: 5,
           ItemName: "Paper A4"
       },
       {
           ID: 1,
           ItemName: "Pencil"
       },
       {
           ID: 3,
           ItemName: "Eraser"
       },
       {
           ID: 6,
           ItemName: "Stapler"
       }
   );
   

2. ClearCollect(
       collDistinctStationeryData,
       RenameColumns(
           AddColumns(
               Distinct(
                   collStationeryData,
                   ItemName
               ),
               "ItemID",
               0
           ),
           "Result",
           "ItemName"
       )
   );
   

3. ClearCollect(
       collDistinctStationeryDataTemp,
       collDistinctStationeryData
   );
   

4. ForAll(
       collDistinctStationeryDataTemp,
       Patch(
           collDistinctStationeryData,
           LookUp(
               collDistinctStationeryData,
               ItemName = collDistinctStationeryDataTemp[@ItemName]
           ),
           {
               ItemID: First(
                   Filter(
                       collStationeryData,
                       ItemName = collDistinctStationeryDataTemp[@ItemName]
                   )
               ).ID
           }
       )
   );  
   

29. 
    
30. If we check the collection, it has:
1. collStationeryData
   
2. 
   
   
3. collDistinctStationeryData (initial stage before Patch) / collDistinctStationeryDataTemp
   
4. 
   
   
5. collDistinctStationeryData (After Patch)
6. 
   
   
31. This way, you can utilize these power packed functions as per the required. Even you may nest these functions to avoid multiple steps.

With this, I am concluding this post.

Happy Coding !!!

Will see you again with some new topics.

Stay Safe !

Stay Healthy !