Forum Discussion

Grella's avatar
Grella
Frequent Visitor
3 years ago

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...
Need Help
Grella's avatar
Grella
Frequent Visitor
3 years ago

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

wdx223_Daniel's avatar
wdx223_Daniel
Community Champion
3 years ago
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
    Custom1