Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 ...
Need Help
  • AlienSx's avatar
    AlienSx
    3 years ago

    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"})

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

As a newbie to Power Query I think i can see that my problem lies in this part of the code? After grouping and carrying out the formula then expanding any nulls that were put in before dont seem to stay in place during the list generate?? I have looked at List.sum and seen another article using ??0 but i cant get either of these to work.

Any help please as this has turned into another late!!! night

 

fq = t[#"Forecast qty RT - MAR"],
oo = t[#"On order (BF required date)"],

#"rtList" = 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]),