Null values in table replaced with 0 return blank cell from calculation??

[Anonymous]

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 the Query is closed and loaded the Null are replaced in the table by 0 but the column that returns the sum of the calculation is still blank?? any ideas how i can get around this please??

let

Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Part", type text}, {"Forecast", Int64.Type}, {"Forecast qty RT - MAR", Int64.Type},
{"Stock Total", Int64.Type}, {"On order (BF required date)", Int64.Type}, {"J24 Quantity", Int64.Type}}),

#"Grouped Rows" = Table.Group(#"Changed Type", {"Part"}, {
{"Tables", (t)=>
let
//lists to compute running stock total
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]),

//combine running total column with original table
#"Result" = Table.FromColumns(
{t[Part]}
& {t[Forecast]}
& {fq}
& {#"rtList"}
& {t[Stock Total]}
& {oo}
& {t[J24 Quantity]},
{ "Part",
"Forecast",
"Forecast qty RT - MAR",
"Running Stock",
"Stock Total",
"On order (BF required date)",
"J24 Quantity"})
in
#"Result",
type table[
#"Part" = text,
#"Forecast" =Int64.Type,
#"Forecast qty RT - MAR"=Int64.Type,
Running Stock=Int64.Type,
Stock Total=Int64.Type,
#"On order (BF required date)"=Int64.Type,
J24 Quantity=Int64.Type]
}}),
#"Expanded Tables" = Table.ExpandTableColumn(#"Grouped Rows", "Tables",
{"Forecast","Forecast qty RT - MAR", "Running Stock", "Stock Total", "On order (BF required date)", "J24 Quantity"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded Tables",null,0,Replacer.ReplaceValue,{"On order (BF required date)"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,0,Replacer.ReplaceValue,{"Stock Total"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",null,0,Replacer.ReplaceValue,{"J24 Quantity"})

in #"Replaced Value2"

PartForecastForecast qty RT - MARStock TotalOn order (BF required date)J24 Quantity

Screw	1	1
Screw	3	3		2
Screw	8	7		2	0
Bolt	7	7		0	0
Bolt	11	11	9	2	0
Bolt	13	13	9	2	0
Bolt	13	13	9	2	0
Clamp	6	6	0	0	0
Clamp	2	2	24		0
Clamp	8	8	31	7	0
Clamp	3	3	40	7	4
Fire Damper	10	10	30	0	30
Fire Damper	5	5	30	0
Fire Damper	3	3		30	30

[AlienSx · Accepted solution]

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]

Hi thanks for the reply, yes I reliased that I needed to cahnge the null to 0 before the calculation.

Many thanks again.

[Anonymous]

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]),

[Anonymous]

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])}),