How to append items to a list of lists?

The ListCombine() formula is great for merging two or more lists or items together, however, it has this peculiar habit of flattening the output. I understand this is helpful most of the time (especially for non-programmers) because it means we don’t have to worry about flattening or not after adding items or lists to our list.

However, sometimes I really do want to add a list as an element in my list of lists.

  • Example 1: I have a list of key-value pairs (from a table or generated otherwise)
  • Example 2: I have a list of date ranges, each represented by a list of two dates: [start, end].

Now say I have two such lists and I want to combine them for one single FormulaMap or something. So far, the best solution I can think of is to use a Sequence formula like this:

Sequence(1, Count(OldList) + Count(NewList)).FormulaMap(
   If(CurrentValue<=Count(OldList),
      OldList.Nth(CurrentValue),
      NewList.Nth(CurrentValue-Count(OldList))))

which basically does exactly the same thing as ListCombine(OldList, NewList) except the output is not flattened… and it takes up more time and space to write :stuck_out_tongue:

Furthermore, if either list is generated by a formula itself, then the above formula becomes ludicrously long.

Anyone have any other creative solutions?

Hold your horses folks! I’ve just discovered the wonderful world of Splice().

Splice is a very straightforward and powerful solution to combining lists together. Here is the new formula to replace the old convoluted one:

Splice(OldList, OldList.Count()+1, 0, NewList)

or equivalently:

NewList.Splice(0, 0, OldList)

You can even insert one list into the other by changing the first number argument start to something other than 0 and delete a few elements while your at it (second number argument deletecount).

Not sure how I passed over this solution. Loving the Splice() :slight_smile:

3 Likes

Dear @Ryan_Martens,

Thanks sharing what you learned back in the community.

One-day other visitors will be thankful for your input :handshake:

3 Likes