Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
hello, @Stubrg 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"})
hello, @Stubrg 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"})
Hi thanks for the reply, yes I reliased that I needed to cahnge the null to 0 before the calculation.
Many thanks again.
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]),
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])}),
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.