Forum Discussion
Null values in table replaced with 0 return blank cell from calculation??
Hi I have a table created by merging data some of cells return Null ihave changed these to 0 with the replace function, hoping that the 0 would be used in a calculation within Power Query. but when ...
hello, Anonymous why don't you replace nulls with 0s across all your columns in the beginning (in between #"Changed Type" and #"Grouped Rows")? unless you don't do it on purpose of course...
= Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"Forecast", "Forecast qty RT - MAR", "Stock Total", "On order (BF required date)", "J24 Quantity"})
I seen a solution using List.Sum but wrapping with this hasnt worked?
#"rtList" =List.Sum({ List.Generate(
()=>[rt=t[Stock Total]{0} - fq{0} + oo{0}, idx=0],
each [idx] < List.Count(fq),
each [rt= if oo{[idx]} <> oo{[idx]+1}
then [rt] - fq{[idx]+1} + oo{[idx]+1}
else [rt] - fq{[idx]+1},
idx=[idx]+1],
each [rt])}),