Hello,
Is it possible to bulk add new calculated columns based on a set of criteria? For example, iterate through a table, search for all date fields and then create a second calculated column based on each date field. Please see the example below.
We create a custom column for many of our date fields, that concatenates the [date] and [year] to create a nice, easy to read date format for display on visualizations and for filtering. We do not want to mess with the original date because that gets used in other reports and users want to see the full date and time.
The formula looks like this:
Date MD = 'Table'[Date].[Month] & " " & 'Table'[Date].[Year]
(the MD suffix stands for Month and Day)
The output looks like this:
January 2018
February 2018
etc.
I would like to automate the creation of that custom column for all date fields in a table. If I were to write this with code, it would look something like this:
foreach (column in table) {
if (column.type == type.date) {
NewColumn = table.createcolumn(column.name & " MD")
NewColumn.formula = Column.Month & " " & Column.Year
{
}
A table that used to look like this:
Title, Start Date, End Date, Due Date
Would now look like this:
Title, Start Date, Start Date MD, End Date, End Date MD, Due Date, Due Date MD
Note: If you have an alternative solution to how I am using dates, that would be great, but I still would like to know if it is possible to iterate through columns and create new ones based on the column's type. Thanks!
