Forum Discussion
Replace null values with conditional average
Help please! I'm still fairly new to Power BI and am trying to replace null values in my data with the average of the previous 3 months with some conditions. I have simplified the 2 tables to demo...
Thanks for your response wdx223_Daniel . Would you mind sharing the code for your first 2 steps (UnitsSold & EmployeeDetails)? I have pulled these two tables into power query as 2 separate tables, so not sure how you are referencing EmployeeDetails within the same table
let
UnitsSold = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
EmployeeDetails = Table.Buffer(Excel.CurrentWorkbook(){[Name="Table2"]}[Content]),
Custom1 = #table(
Table.ColumnNames(UnitsSold),
List.Accumulate(
Table.ToRows(UnitsSold),
{{},{},""},
(x,y)=>let
a=EmployeeDetails{[Employee=y{0},Employment Status="Resigned"]}?[#"Emp. End date"]? ??DateTime.LocalNow(),
b=List.Average(List.LastN(x{1},3))
in
if y{1}>=a then {x{0}&{y},{},y{0}} else
if y{0}<>x{2} then {x{0}&{y},{y{2}},y{0}} else
if y{2}<> null then {x{0}&{y},x{1}&{y{2}},y{0}}
else {x{0}&{List.FirstN(y,2)&{b}},x{1}&{b},y{0}}
){0}
)
in
Custom1Would you still happen to have this file and can attach? I still can't seem to replicate the result, and i also don't fully understand the code in laymans terms to be able to troubleshoot exactly where i'm going wrong
wrote this code in a temp workbook without saving. sorry for that