New formulas for columns

Goal

Dynamically select a column from a table

Issue

The current possible approach forces you to repeat yourself into a coupled mess:

Select Options Formula

List("Population", "Area", "Density", "GDP", "Elevation", "Temp")

Dynamic Column Formula

Switch(
  [Select],
  "Population", thisRow.Population,
  "Area", thisRow.Area,
  "Density", thisRow.Density,
  "GDP", thisRow.GDP,
  "Elevation", thisRow.Elevation,
  "Temp", thisRow.Temp
)

If you want to add a column you need to modify information in at least three different places, very prone to error and a nightmare to maintain, not ideal!

Proposition

Add new formulas:

  • Columns(Table)
  • ColumnName(Column)
  • ColumnType(Column)
    …

With these formulas we could make a truly dynamic solution - the above example could become:

Select Options Formula

Columns([Table]).Filter(ColumnType(CurrentValue) = "number").FormulaMap(ColumnName(CurrentValue))

Dynamic Column Formula

Columns([Table]).Filter(ColumnName(CurrentValue) = [Select]).First()

Notes

Working example of the current solution

Columns(Table)

Would return a list of Columns, equivalent to:

List([Table].Population, [Table].Area, ...)

Would probably require the engine to be smart about how it represents a column, I have no idea how it currently works under the hood. I’m afraid it will process all cells in the column for each column, when all you might want is the name of each column

Doc Explorer Pack

@Scott_Collier-Weir’s pack could help with the Select Options Formula, but not the Dynamic Column Formula I believe

Additional formulas

If this was to be implemented then there are additional similar formulas that could be considered, as well as thisColumn - That would be amazing!

1 Like