Jun 24 2020 02:23 PM - last edited on Jun 24 2020 02:36 PM by Allen
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.
Jun 24 2020 08:51 PM - edited Jun 24 2020 08:53 PM
@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]}) ...)
Jun 24 2020 08:56 PM
@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".
Jun 25 2020 08:23 AM
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.