In column calculations

Hits: 596

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!

Deine Bewertung
[Total: 0 Average: 0]

Leave a Reply

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

This website stores some user agent data. These data are used to provide a more personalized experience and to track your whereabouts around our website in compliance with the European General Data Protection Regulation. You may find further informations here: Privacy Policy   If you decide to opt-out of any future tracking, a cookie will be set up in your browser to remember this choice for one year. I Agree, Deny
840
%d bloggers like this: