Combining two collections based on order alone

There are times that you have two sets of data that you want to turn into one and they are actually “matching” in order, but have no common field. The number of fields in each does not matter, however you need to pick one (generally the one with the most fields), then add the fields from the other one. The example below only has one field in the first and two in the second, but show the principle of adding consecutive numbers to each and then joining them.

This uses two collections – the collection process is only an example for testing and producing the resulting data sets to demonstrate the code needed to combine them.

ClearCollect(
   col1,
   {Field1: "a"},
   {Field1: "b"},
   {Field1: "c"},
   {Field1: "d"},
   {Field1: "e"}
);
ClearCollect(
   col2,
   {Field2: 11, Field3:"Data1"},
   {Field2: 12, Field3:"Data2"},
   {Field2: 13, Field3:"Data3"},
   {Field2: 14, Field3:"Data4"},
   {Field2: 15, Field3:"Data5"}
);

The resulting collections will have five records each and then we need to make these one collection of five records with both fields.

With(
   {
      _C1: 
      ForAll(
         Sequence(
            CountRows(col1)
         ),
         Patch(
            Index(
               col1,
               Value
            ),
            {RowNo1: Value}
         )
      ),
      _C2: 
      ForAll(
         Sequence(
            CountRows(col2)
         ),
         Patch(
            Index(
               col2,
               Value
            ),
            {RowNo2: Value}
         )
      )
   },
   ClearCollect(
      col3,
      AddColumns(
         _C1,
         OtherField2,
         LookUp(
            _C2,
            RowNo2 = RowNo1
         ).Field2,
         OtherField3,
         LookUp(
            _C2,
            RowNo2 = RowNo1
         ).Field3
      )
   )
)

I have only used one field in the first list, but it could be 100 – they will all come across and the two fields from the second collection will be added to them. If you have more than two fields in the second collection, just repeat the “OtherField” Lookup.