It sounds like you’re working with a matrix table. The best way to deal with matrix data is to unpivot it first (using Unpivot transformation), transform as needed (all data will be in 1 column), then pivot it back as the last step.
Tip: You may want to use aggregation type Any for pivoting back.