PowerQuery: Sum Calculated Columns

%3CLINGO-SUB%20id%3D%22lingo-sub-1487578%22%20slang%3D%22en-US%22%3EPowerQuery%3A%20Sum%20Calculated%20Columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1487578%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20all.%20I%20have%20cleaned%20some%20data%20in%20power%20query%20and%20from%20the%2010%26nbsp%3B%20original%20data%20columns%2C%20I%20created%2010%20more%20calculated%20columns%20(converting%20some%20raw%20fertilizer%20columns%20into%20converted%20nitrate%20columns).%20I%20completed%20this%20task%20successfully%20but%20now%20I%20need%20a%20column%20that%20adds%20across%20the%20columns%20for%20each%20row.%26nbsp%3B%20I%20tried%20the%20simple%20adding%20method%20but%20all%20I%20get%20are%20nulls.%20Thanks%20for%20any%20help%20provided.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1487578%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1487978%22%20slang%3D%22en-US%22%3ERe%3A%20PowerQuery%3A%20Sum%20Calculated%20Columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1487978%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676026%22%20target%3D%22_blank%22%3E%40slohombre%3C%2FA%3E%26nbsp%3BDo%20you%20have%20%22null%22%20in%20the%20individual%20calculated%20column(s)%3F%20If%20so%2C%20it%20may%20cause%20your%20sum%20to%20be%20null.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20you%20could%20either%20fix%20the%20%22null%22%20in%20each%20individual%20column%20first%20by%20wrapping%20them%20with%20if%20...%20is%20null%20then%200%2C%20then%20in%20your%20sum%20column%20simply%20add%20them%2C%20or%20try%20to%20revise%20your%20formula%20bar%20from%20Table.AddColumn(...%2C%20each%20%5Bcalculated%20column%20A%5D%20%2B%20%5Bcalculated%20column%20B%5D...)%20to%20Table.AddColumn(...%2C%20each%3C%2FP%3E%3CP%3E%3CSTRONG%3EList.Sum(%7B%5BA%5D%2C%20%5BB%5D%7D)%20...)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1487981%22%20slang%3D%22en-US%22%3ERe%3A%20PowerQuery%3A%20Sum%20Calculated%20Columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1487981%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676026%22%20target%3D%22_blank%22%3E%40slohombre%3C%2FA%3E%26nbsp%3BSelect%20the%20columns%20you%20want%20to%20add%20the%20row%20values%20for%20into%20a%20new%20column.%20In%20the%20%22Add%20Column%22%20tab%2C%20press%20the%20%22Standard%22%20icon%20in%20the%20%22From%20Number%22%20section%20and%20choose%20%22Add%22.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-06-25%20at%2005.52.14.png%22%20style%3D%22width%3A%20197px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F200899i96DAA243A87B190F%2Fimage-dimensions%2F197x289%3Fv%3D1.0%22%20width%3D%22197%22%20height%3D%22289%22%20title%3D%22Screenshot%202020-06-25%20at%2005.52.14.png%22%20alt%3D%22Screenshot%202020-06-25%20at%2005.52.14.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1489444%22%20slang%3D%22en-US%22%3ERe%3A%20PowerQuery%3A%20Sum%20Calculated%20Columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1489444%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676026%22%20target%3D%22_blank%22%3E%40slohombre%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBit%20more%20to%20above.%20Simplest%20way%20is%20to%20use%20Add%20as%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3Bsuggested.%20However%2C%20if%20you%20add%20only%20two%20columns%2C%20in%20generated%20formula%20it%20will%20be%20like%20%3CSTRONG%3Eeach%20%5BColumn1%5D%20%2B%20%5BColumn2%5D%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EAnd%2C%20as%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675276%22%20target%3D%22_blank%22%3E%40hynguyen%3C%2FA%3E%26nbsp%3Bexplained%2C%20any%20number%20plus%20null%20returns%20null.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHowever%2C%20if%20click%20Add%20on%20more%20than%20two%20columns%2C%20List.Sum()%20will%20be%20generated%20automatically%2C%20which%20doesn't%20care%20about%20nulls.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThus%20result%20is%20number%20of%20columns%20specific.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1489881%22%20slang%3D%22en-US%22%3ERe%3A%20PowerQuery%3A%20Sum%20Calculated%20Columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1489881%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1489882%22%20slang%3D%22en-US%22%3ERe%3A%20PowerQuery%3A%20Sum%20Calculated%20Columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1489882%22%20slang%3D%22en-US%22%3EThank%20you!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1490220%22%20slang%3D%22en-US%22%3ERe%3A%20PowerQuery%3A%20Sum%20Calculated%20Columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1490220%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676026%22%20target%3D%22_blank%22%3E%40slohombre%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

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

@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]}) ...)

@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".

Screenshot 2020-06-25 at 05.52.14.png

@slohombre 

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.

@Sergei Baklan Thank you!

Thank you!

@slohombre , you are welcome