Here is a Power Query ETL workaround that I implemented to transform multiple columns using a single step based on another column in the same table. I had to deal with this challenge in my most recent Power BI project on Financial Reporting. When using just the interface (GUI), it may take more steps and longer to accomplish the desired result in Power Query. Using a bit of “M” coding, you can get the results quickly and efficiently.
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.
#"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
Fowmy Abdulmuttalib is a Microsoft-certified business intelligence professional with extensive experience in finance, accounting and data analytics. Currently, he is employed by McDonald’s Saudi Arabia as the Head of ERP and Business Intelligence. He is interested in everything data-related, he likes to share his passion and knowledge with the data community.