Custom column

[Janosa]

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

[HotChilli · Accepted solution]

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.