Hits: 2353

If you want to perform calculations in Power Query, usually you have to add a new column and delete the original one. In this short blog entry, I’ll show you a way to perform in column calculations.

Typical way

Given is a table as follows:

Original table
Original table

We just want to multiply each value by 10. To achieve this, usually we would add a new column:
= Table.AddColumn(chType, "new Column", each [Amount]*10)

and end up with this:

add new column
add new column

Then we would delete the original column and rename the new one to “Amount”…….

In column calculations

A more effective way would be to perform “in column calculations”.
All we have to do is to write this little function:

=Table.FromRecords(
Table.TransformRows(chType,
(in_place) =>
Record.TransformFields(in_place,
{"Amount", each in_place[Amount] * 10}))
)

The function transforms each row of the column “Amount” and multiplies the value with 10 – in place!

in_column_replacement
in_column_replacement

That’s all. No need to delete a column!

Do you want to learn more about Power BI or Power Query?
Take a look here.

Happy Querying!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: