3 Ways to SUM null values in Power Query

[@BIGorilla]

To learn more about functions and syntax make sure to check out: powerquery.how/ Enjoy!

[@ramabg2]

OMG. null + 10 = null is the biggest plot twist ever. Thank you for the tips

[@BIGorilla]

What issues do you experience with null values? I'd love to hear from you in the comments! 👌

[@kudifilipe9875]

Several collums in a sheet with " NULL" value... Somes in the beginning, midlle, ....

[@mnowako]

Awesome! Thank you! List.Sum seems to be the best option.

[@veemozz9489]

Thank you so much. This was great help for a project I'm working on.

[@BIGorilla]

Great to hear it's helping you. It can be a very tricky matter that's easy to forget about!

[@0ctopus001]

This is so great! I was working with dynamic values and permutations. Not being able to handle calculation with null meant calculating with 0 and in some cases many millions more rows. So great.

[@BIGorilla]

Millions of rows, that may have a big performance implication. Were the results better with List.Sum? Thanks for posting how it helped :)

[@0ctopus001]

@@BIGorilla Definitly. My Problem was more based on the dynamic weight for the different permutation resulting on some values being sometimes null. Beside solving this problem I am really thankfull for the solution being a good way for solving comming challenges.

[@leebecker8255]

Brilliant, thank you! I was having problems with the null values, so it's very useful to know that List.Sum handles it. Previously I was replacing the nulls with 0, but I didn't really want to do that because it takes some information away :)

[@DanielBoureanu]

love all your videos, brilliant stuff, many thanks !

[@Quijanos1]

Outstanding and thank you!

[@jayshah9462]

Great help

[@shuvomridharaaj7550]

Awesome!

[@BIGorilla]

O yes o yes! 🔥

[@diegolozano2397]

thanx a lot

[@aravyaverma4637]

How do i sum strings values in a column which also includes null values? Please help

[@BIGorilla]

Can you elaborate? Generally you don' t sum strings. The sum operation works on numbers. Do you mean you have numbers formatted as text?

[@RaisaMaduro]

Question one: How can Power Query finds numbers combinations in a database with 6 columns with 350 rows? For example, column A: 6 numbers 7 11 24 26 35 40; column B also 6 numbers: 3 14 18 19 32 40; column C: 5 9 11 13 44 45, column D: 4 20 22 31 33 40, column E: 4 12 23 28 33 41, column F: 7 8 23 26 38 43, column G: 4 10 23 31 43 44; column H 13 25 26 27 32 35; column I: 13 25 26 27 32 38;etc.

[@BIGorilla]

Hi Raisa, good question. You could apply a filter something like this: = Table.SelectRows(#"PreviousStepName", each List.Contains( {7, 11, 24, 26, 35} , [A] ) and List.Contains( {3, 14, 18, 19, 32, 40} , [B] ) and List.Contains( {5, 9, 11, 13, 44, 45} , [C] ) ) etc etc This will return only rows that have at least 1 matching value per specified column. Thanks for your question , good luck!

[@RaisaMaduro]

​@@BIGorilla The first time I started working with Power Query as if it were Excel is with your video "2 Tricks to Create Unique Combinations From Multiple Columns". So the question is how to apply Table.SelectRows? By the way, the columns must be compared with each other. Is Table.SelectColumns not the correct function?

[@BIGorilla]

@@RaisaMaduro Power Query automatically applies the Table.SelectRows function when you perform a filter operation. When a certain condition is met, it will return the rows. If you like, you can explore some more basic Power Query videos on KZbin to find out. Hope that works!

[@mivanows]

Please help! I want to replicate the same result in Powerquery. Here is the example in Excel: 56 77 46 3 ->formula =COUNT(A1:C1) 33 54 2 ->formula =COUNT(A2:C2) Note that the A2 is [empty] - the count result is as expected (=2) I can't find a way to replicate it in Powerquery. Powerquery returns 3 - it is considering the empty as [NULL] and counting - the result is =3, but it should be =2.