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 demonstrate my scenario:
- Units Sold table: shows the number of units sold by a particular employee in a particular month
- Employee Details table: shows if the employee is still employed with the company or has left (and the date their employment ended)
In my Units Sold table, i want to replace all the null values with the average from the previous 3 months data for that particular person, even if one month in the previous 3 is also a calculated average (see desired value for Jessica in 1/05/2021). Before calculating this average, i would need to be able to check the Employee Details table to ensure the person is still employed - any null values after the Emp. End date should remain null.
| Employee | Date | Units Sold | Expected Value |
| Jessica | 1/01/2021 | 2875 | |
| Jessica | 1/02/2021 | 3452 | |
| Jessica | 1/03/2021 | 1217 | |
| Jessica | 1/04/2021 | null | 2515 |
| Jessica | 1/05/2021 | null | 2395 |
| Jessica | 1/06/2021 | 5816 | |
| Jessica | 1/07/2021 | 1056 | |
| Jessica | 1/08/2021 | null | 3089 |
| Jessica | 1/09/2021 | 4956 | |
| Jessica | 1/10/2021 | 2534 | |
| Jessica | 1/11/2021 | 7512 | |
| Jessica | 1/12/2021 | 3185 | |
| Abigail | 1/01/2021 | 5672 | |
| Abigail | 1/02/2021 | 8495 | |
| Abigail | 1/03/2021 | 2356 | |
| Abigail | 1/04/2021 | null | 5508 |
| Abigail | 1/05/2021 | 4675 | |
| Abigail | 1/06/2021 | 1523 | |
| Abigail | 1/07/2021 | null | null |
| Abigail | 1/08/2021 | null | null |
| Abigail | 1/09/2021 | null | null |
| Abigail | 1/10/2021 | null | null |
| Abigail | 1/11/2021 | null | null |
| Abigail | 1/12/2021 | null | null |
| Employee | Employment Status | Emp. End date |
| Jessica | Employed | |
| Abigail | Resigned | 29/06/2021 |
Any help on the best way to tackle this in power query would be greatly appreciated!
5 Replies
= #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})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 Custom1
