Indeed. Here’s a versatile way to loop:
That’s a know thing:
There’s a workaround with storing the outer CurrentValue in a cell somewhere (e.g. same row dedicated column, or a helper table).
E.g. here’s the way I’m iterating over Value Drivers, then each of their Metric Uplifts, then for each of those I insert six rows with a Sequence().FormulaMap(). Note how I store _Current Value Driver and _Current Metric Uplift into a cell before using it in a nested FormulaMap:
[image]
[image]
(Fun fact: in retrospective, in my case none o…
It may not always be needed though. E.g., here’s a different approach that could be used e.g. if you need to iterate over a product of 2+ lists. It’s a live calculation, no action required:
Alright, here’s one more way to make a list of all combinations, for any number of categories. It takes a recursive approach to calculate the product of thisRow x nextRow and gives you the result in the first row of the table (a list of lists):
At this point it may feel like I’m just flexing — for your specific case it could’ve been done easier. But I want to show a general case solution so that anyone could learn from it.
As for copying over the row, here’s a handy trick (see the video):
Not a hack per se, just a smart way to speed up formula creation to copy a row (e.g. from the main table to a log table), using some regex.
Here’s a video how you can make such log table in 2 minutes:
Copy the table to a text editor or right into an offline / online editor that supports regular expressions (I prefer regex101.com , not affiliated). Remove everything but the headers
Set up replacement with regular expressions:
Search: ([^\t]+)\t?
(means capture each element between tabs) …