Transform Multiple Columns Based On Other Columns In Power Query With A Single Step

[Fowmy]

Using a simple dataset, I am replicating the problem. The challenge is to convert the numbers in the Actual and Budget columns in the original table to negative numbers if the corresponding value in the Type column is “Revenue” as shown in the table on the right.
 

If you are trying to do it using the GUI, you can either unpivot the Actual and Budget columns, add a custom column to calculate desired values, and then pivot the columns again. The problem with this approach is that UnPivot and Pivot are slow, and they can fail if the rows do not have uniqueness.

A second option is to create custom columns for each column that needs to be transformed, delete the original columns, and rename the newly created columns. This method is faster, but you have longer steps.

In my solution approach, a single transformation step transforms all required columns in one step, as shown in the below “M” code script. I would like to thank “M” contributors whose contributions were helpful in coming up with this solution.

let
  #"Changed Type" = --Source ,
  ColumnNames = {"Actual", "Budget"},
  fn = (fnRec as record) as list =>
    List.Transform(ColumnNames, each {_, each _ * (if fnRec[Type] = "Revenue" then - 1 else 1)}),
  RevenueAdj = Table.FromRecords(
    Table.TransformRows(#"Changed Type", (Rec) => Record.TransformFields(Rec, fn(Rec)))
  )
in
  RevenueAdj

I actually broke the solution into three steps for clarity:

I created a variable named “ColumnNames” to hold the names of the columns that need to be transformed. Created a function called  “fn” with a record variable parameter to transform each column to negative number based on the value “Revenue” in the Type column. Next, the transformation step; I used two table and one record functions to achieve the results. 

Table.TransformRows function can receive a table as an input and apply transformations row-by-row.

Record.TransformFields function receives a record as an input within the Table.TransformRows function and performs transformations on the selected columns, this is where I call the function “fn” to accomplish the task.

Finally, I used Table.FromRecords to convert the records back to a table. This is required as the Table.TransformRows converts the table into records, so you need to turn it back to a table.

Hope this article is useful and provides some new knowledge on “M” coding. I have attached the Power BI below

Download PBIX file