New formulas for columns

Suggestion Box
1

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