Forum Discussion

MIkkelHyldig's avatar
MIkkelHyldig
Helper II
4 years ago
Solved

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 filte...
Need Help
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    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.

v-zhangti's avatar
v-zhangti
Community Support
4 years ago

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's avatar
MIkkelHyldig
Helper II
4 years ago

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