Forum Discussion
PowerQuery: Sum Calculated Columns
Hi, all. I have cleaned some data in power query and from the 10 original data columns, I created 10 more calculated columns (converting some raw fertilizer columns into converted nitrate columns). I completed this task successfully but now I need a column that adds across the columns for each row. I tried the simple adding method but all I get are nulls. Thanks for any help provided.
6 Replies
Bit more to above. Simplest way is to use Add as Riny_van_Eekelen suggested. However, if you add only two columns, in generated formula it will be like each [Column1] + [Column2]
And, as hynguyen explained, any number plus null returns null.
However, if click Add on more than two columns, List.Sum() will be generated automatically, which doesn't care about nulls.
Thus result is number of columns specific.
SergeiBaklan Thank you!
slohombre , you are welcome
slohombre Select the columns you want to add the row values for into a new column. In the "Add Column" tab, press the "Standard" icon in the "From Number" section and choose "Add".
- Thank you!
slohombre Do you have "null" in the individual calculated column(s)? If so, it may cause your sum to be null.
I think you could either fix the "null" in each individual column first by wrapping them with if ... is null then 0, then in your sum column simply add them, or try to revise your formula bar from Table.AddColumn(..., each [calculated column A] + [calculated column B]...) to Table.AddColumn(..., each
List.Sum({[A], [B]}) ...)
