PowerApps: Add/Merge/Join Two Collections With Sequence Number

Hello Friends,

Welcome back with another post on PowerApps. Sometimes, we have requirement where we have data in 2 or more collections. Now, we have to club all these collections and get only single collection so that the same can be displayed in grid or can perform any further action. This can be done simply using ClearCollect-

1. ClearCollect(MergedCollection, Collection1,Collection2);

The issue arises when we have to maintain a sequence number with the collection items. Well, this can also be done in a very simple manner. 🙂

1. Let's we have a SharePoint list having records.
2. 
3. The same is fetched in PowerApps.

4. ClearCollect(
       DataFromExcelSelectedColumns,
       ShowColumns(
           DataFromExcel,
           "FirstName",
           "LastName",
           "FullName"
       )
   );
   

5. We have fetched only 3 columns (FirstName, LastName, FullName) and store the data in collection named "DataFromExcelSelectedColumns".
6. 
7. Now we will add a column named "RowNumber" which will assign the items a sequential number. The new item collection is stored in a new collection named "DataFromExcelSelectedColumnsSequence". This collection will be used for merging.

8. ForAll(
       DataFromExcelSelectedColumns,
       Collect(
           DataFromExcelSelectedColumnsSequence,
           Last(
               FirstN(
                   AddColumns(
                       DataFromExcelSelectedColumns,
                       "RowNumber",
                       CountRows(DataFromExcelSelectedColumnsSequence) + 1
                   ),
                   CountRows(DataFromExcelSelectedColumnsSequence) + 1
               )
           )
       )
   );
   

9. The output of this collection is-
10. 
11. As we can see, a new column "RowNumber" is added with a sequence number starting from 1 onwards.
12. Now suppose, we have another data collection (may be locally generated or data received from Power Automate) named "TempDataColl". This collection is also having 3 columns (FirstName, LastName, FullName). Let it be as-

13. ClearCollect(
        TempDataColl,
        {
            FirstName: "Praveen",
            LastName: "Chinta",
            FullName: "Praveen Chinta"
        },
        {
            FirstName: "Rajeev",
            LastName: "Bhuvan",
            FullName: "Rajeev Bhuvan"
        },
        {
            FirstName: "Suyash",
            LastName: "Bharti",
            FullName: "Suyash Bharti"
        }
    );
    

14. The collection thus created is-
15. 
16. Similar way, we will add the "RowNumber" column to this collection also and save it in new collection named "DataFromExcelSelectedColumnsSequence2". The point to remember is that, the sequence number should start from the next sequence of the previous collection (DataFromExcelSelectedColumnsSequence) sequence number. In our case, it should start from 8 as the previous collection was having 7 records. For this, we will use below function-

17. ForAll(
        TempDataColl,
        Collect(
            DataFromExcelSelectedColumnsSequence2,
            Last(
                FirstN(
                    AddColumns(
                        TempDataColl,
                        "RowNumber",
                        CountRows(DataFromExcelSelectedColumnsSequence) + 1 + CountRows(DataFromExcelSelectedColumnsSequence2)
                    ),
                    CountRows(DataFromExcelSelectedColumnsSequence2) + 1
                )
            )
        )
    );
    

18. As you can see, while defining the sequence number, we have added the rows count of previous collection.
19. 
20. This way, we got our second collection. Now we will simply merge both collections into one-

21. ClearCollect(FinalCollection,DataFromExcelSelectedColumnsSequence,DataFromExcelSelectedColumnsSequence2);
    

22. The collection "FinalCollection" will be having items of both collections.
23. 
    
    
    
24. At last, we will clear all the intermediate collections-

25. ClearCollect(
        DataFromExcelSelectedColumnsSequence,
        []
    );
    ClearCollect(
        DataFromExcelSelectedColumnsSequence2,
        []
    );
    ClearCollect(
        TempDataColl,
        []
    );
    

26. This way, we can achieve the desired functionality.

With this, I am concluding this post.

Happy Coding !!!

Will see you again with some new topics.

Stay Safe !

Stay Healthy !