Replace in multiple columns


I have to replace values in multiple columns.
E.g. I have a dummy dataset below. If for all COLX (where X is e number) the value A occurs, I have to replace the value in columns energiebronX (where x is a number) some value XYZ

I thought about unpivot but I didn’t find a solution that could work for any number of columns to replace values in.

Maybe unpivot and then create a group flag (e.g. col1, energiebron 1 had group_flag = 1) en then (col2, energiebron2 has group flag 2) en then iterate by group flag passing the dataset to a submodule and then filtering by group flag in that module. Then do the replacement in the submodule and get all the data back in the main module.

Or is there a much simpler solution ?


I would unpivot all columns (except for RowNo), removed x from COLx and energiebronx into a separate column (group number), and then repivoted back, so that group remains a column, but COL and energiebron become 2 columns. Then a simple expression would modify energiebron as necessary. Then unpivot back and repivot into the original column order.

Thanks ! I shall give it a try