Forum Discussion
Add custom column with max date per item in Power Query
Hi,
I have the following table 1 where and I need to add a custom column with the latest date per Item No.
Table 1 is related to table 2 with Item No and Variant and a date column. When I filter on date column in table 2 on "before 1-2-2021" it should filter out both Item 5001 and not just Variant 5001-002.
Table 1: Last Purchase date
| Item No | Variant | Last Purchase Date | NEW COLUMN |
| 5001 | 5001-001 | 24-01-2020 | 25-02-2021 |
| 5001 | 5001-002 | 25-02-2021 | 25-02-2021 |
| 5002 | 5002-001 | 01-04-2020 | 01-04-2020 |
I have already made a calculated column in Power BI for table 1 but it only filters out 5001-002 and not both as I want.
Thanks, Mikkel
Hi, MIkkelHyldig
If you want to generate the maximum date column with power query, you can try the following method.
Step 1:
Step 2:
Step 3:
The date column of Table 2 establishes a relationship with the MAX column.
Step 4:
Is this the result you were expecting?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Hi MIkkelHyldig ,
Is there a relationship between these two tables: 1 and 2 ?
If yes and its basis the date then it will filter out only one row for 5001 with date as 24 January 2020.
Hi jaideepnema
Yes its a 1-2. The datamodel im still working on.
But the custom column is my real issue here.If you are trying to filter basis the second table and on the date column it will filter basis the relationship you have created. So can you please give us more details regarding the issue you are facing so its easier for us to understand ?
