Power query zero versus null in calculaton

Hi I have a question regarding the use of null,

if I have N columns containing numbers and zeros and multiply them I end up with 0 , all good,
but if I want he 0's ignored I can change these to null values and then,

Please Login or Register  to view this content.

but have tried an accumulation;

Please Login or Register  to view this content.

in the above each column contains 1 or 0 , and another table values 2,3,4, and this works, but if I change the 0's to nulls
any row containing a null returns a total of null?
Why does this happen,

I have attached the sheet with bot versions, I have a further questing regarding null but will leave that for later.

Richard

You say it cannot be calculated but if I have four columns in excel containing values ; 1 , 2 , blank and 4 ,
these would be in pq ; 1, 2 , null , 4 which I cant the get the product of

Please Login or Register  to view this content.

so in the above it is ignored, and I get 8 1 * 2 * 4.

So why in my first question why is it not ignored instead of giving a result of null.

Richard.

but if you have an empty cell in excel loaded to pq it is turned to null and then in the multiplication they do not affect the result,
but in the accumulation they do have an affect on the result in he initial question they do affect the result.

Thanks for all your help, I know I can go on a bit, if I ever get answer I'll post it. shall have to re watch Mike Girvins
vid on blanks, zero text strings etc.

Richard.

there are these two he may have done some more up date as these are just for excel, I watched these some time ago.

https://www.youtube.com/watch?v=ubrpg2ffyNc&t=43s

https://www.youtube.com/watch?v=DOtrWoXSBjA



Richard.

just finish, you may find this interesting.

I loaded a small table contain a space between letters in cells, and also a blank cell,

when converted to lists ;

Please Login or Register  to view this content.

the blank cell becomes a null but the space is blank, when a TEXT.VALUE is run, the blank = text .

Its easier t see so Ivw attached it.

Richard.