General
The [Table].FindOptionalColumn can be used in Column Numbers property of a Table Element to Find the number of the column according to the header and use it together with the CreateList function.
Syntax
The syntax of the [Table].FindOptionalColumn function is:
[Table].FindOptionalColumn ("header")
Examples
In a table with 2 columns: Dimension 1 and Measure 1, [Table].FindOptionalColumn ("Dimension 1") will return 1:
[Table].FindOptionalColumn ("Dimension 1")
It is possible to use expressions in case the Headers are dynamic:
CreateList ( [Table].FindOptionalColumn (“name of column1”) , [Table].FindOptionalColumn (“name of column2”), [Table].FindOptionalColumn (“YTD Sales ” & Date.Now.Year) )
If one of the columns is not found, the error will be ignored and the column will not be included in the table. In contrast, when using FindColumn, the error will throw an exception and the Report will be cancelled.