Hello. I wasn’t sure how to search for this. Wondering–when modifying a column, is there a way to reference the column name in the expression generically so that the same expression can be more easily copy/pasted to modify other columns the same way?
For example, when modifying the column “EmpNo”, I’d like to write an expression like:
“if(contains(COLUMN,’ ‘),asnumber(replace(COLUMN,’ ‘,’’)),asnumber(COLUMN))” where COLUMN refers to “EmpNo” (or whatever column is being modified).
If I understood you correctly you are looking to modify all columns with same formula. For me best way would be enumerate table then unpivot by [RowNo] then update Data then pivot.
Thank you. I do use that option sometimes, but it’s not always appropriate for my needs.
You can try the "Iterate columns" action. It's intended for applying the same calculation to many columns in a generic way.
Also, if you have only a few columns, you can simplify the expression to make it easier to copy/modify. In the example below, you only need to edit the column name in one place.
LET a = [COLUMN]
if(contains(a,’ ‘),asnumber(replace(a,’ ‘,’’)),asnumber(a))”
VERY helpful. Thank you so much!
You're welcome! 