Transforming Multiple Columns without adding new columns in Power Query

Hi Anonymous ,

In your current data format, you would not be able to apply this to multiple columns at the same time - you would need to create a new replace step for each [SUP2], [SUP3] etc.

However, you should really unpivot your SUP columns (multi-select [PART NUMBER] and [Operator] then Transform tab > Unpivot Columns (dropdown) > Unpivot Other Columns).

This will firstly allow you to perform your multiplication replacement step generically on a single column ([Value]), but also restructure your data into the most efficient format for reporting.

Pete

Unpivoting is definitely the best way to go but it is possible to do multiple columns simultaneously even without explicitly referencing each one if you do some fancy row transformations.

Table.FromRecords(
    Table.TransformRows(
        #"Previous Step Name",
        (row) => Record.TransformFields(
                     row,
                     List.Transform(
                         List.Skip(Record.FieldNames(row), 2),
                         (name) => {name, each Record.Field(row, name) * row[Operator]}
                     )
                 )
    )
)