Forum Discussion
Custom column
Hello everyone,
i'm just a beginner with power query so i'm not sure how to solve this issue i have.
I'm looking to add a custom column where if the weight value is zero then it should take the average weight of all products with the same code on that exact date.
for example:
| Date | Product code | weight | Custom Column |
| 1-sep | 1234 | 0 | average weight based on product code date 1-09 = 1 |
| 1-sep | 1234 | 1 | 1 |
| 2-sep | 1234 | 2 | 2 |
| 2-sep | 1234 | 0 | average weight based on product code date 2-09 = 2 |
| 3-sep | 1234 | 1 | 1 |
| 3-sep | 1234 | 2 | 2 |
| 3-sep | 1234 | 0 | average weight based on product code date 3-09 = 1,5 |
is this possible in power query or should I approach it differently?
thanks in advance
I think you want the average of the non-zero entries for the same date.
Here's what I would do:
Duplicate the query.
Filter out the 0 entries (from the column header).
'Group By' Date and Product Code with a new column for the Average of Weight
---
Merge this table back to the original table on (Date, Product Code) to get the Average on each row.
1 Reply
I think you want the average of the non-zero entries for the same date.
Here's what I would do:
Duplicate the query.
Filter out the 0 entries (from the column header).
'Group By' Date and Product Code with a new column for the Average of Weight
---
Merge this table back to the original table on (Date, Product Code) to get the Average on each row.