Add custom column with max date per item in Power Query

[MIkkelHyldig]

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

[jaideepnema]

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. 

[MIkkelHyldig]

Hi @jaideepnema 

Yes its a 1-2. The datamodel im still working on. 
But the custom column is my real issue here. 

[jaideepnema]

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 ?

[MIkkelHyldig]

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

[v-zhangti · Accepted solution]

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.

[MIkkelHyldig]

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