While I was creating my dependency graph I noticed that I had to copy the same formulas many times and slightly altering them. And those were very long and complex formulas, so that took a lot of time and it was really easy to make a mistake.
That’s why I propose a new function -
RunFormula(). This function would calculate value in current row in column passed as first argument.
But how does that differ from
This function would also accept additional arguments used to modify the formula.
This format would be similar to the one of
SwitchIf - arguments come in pairs, the first one represents value to be replaced and the second value to replace the first one with.
That means you could replace certain values (such as columns, rows or tables) with different values of the same type. This would allow to use formulas in a very function-like style.
A simple example
Let’s say we have a table with fours columns - “Value 1”, “Value 2”, “Formatted value 1” and “Formatted value 2”.
“Formatted value 1” has a formula
if(thisRow.[Value 1].IsBlank(),"None :(",thisRow.[Value 1]+"$"), which nicely displays data for column “Value 1”.
Now we want to do the same for “Formatted value 2”. With this function, instead of having to write
if(thisRow.[Value 2].IsBlank(),"None :(",thisRow.[Value 2]+"$") you can write
RunFormula("Formatted value 1", thisRow.[Value 1], thisRow.[Value 2]).
Why is it better?
All updates to formula in “Formatted value 1” are automatically applied to “Formatted value 2” (which is extremely time saving if you have more than two similar formulas) and for more complex formulas you don’t have to worry about accidentaly changing and breaking the formula (which can save a lot of debugging time).
A more complex example
At the end I would also like to link to a real-life example when such a function would be very useful, the previously mentioned dependency graph.
Please note the differences in formulas in
Suggestion box/Formula dependency sections. The second one is there to show how it would like if such a function was implemented.