Forum Discussion
Add custom column with max date per item in Power Query
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.
Sure, jaideepnema
What im trying to do is create a custom column (NEW COLUMN) in power query returning the max date based on Item No:
Table:
| 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 |
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.
Sorry for the late reply! It worked, and even better than my own M-query;
= Table.AddColumn(#"Filtered Rows", "maxInt", each [
filter = [Item No_],
max=Table.Group(
#"Filtered Rows", {"Item No_"},
{{"MaxFiltered", each List.Max([Last Purchase Date])}}
){[Item No_=filter]}[MaxFiltered]
][max])
Many thanks,
Mikkel