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!