When I have a table with duplicates by category for instance, before to load the data, I want to filter them out to keep only the one with the last recent date. I have this data:

To get the last date, follow those steps:

1. I will select the category, in my example, it is the “number” column then I will click on “home -> group by”:

2. On the popup, click on “advanced -> add aggregation” then configure like in the picture (for the “new column name” field, put whatever you want):

NOTE: to get the first date instead of the last one, in the “operation” field, select “min” instead of “max”

3. Click on the “expand” icon of the “groupby” column to select all columns except the “number” one:

4. Click on the “expand” icon of the “date1” column to unselect whatever you want:

5. In the formula bar, change the “red” part by: [argument1]=[argument2]

6. Once done, I can keep like this or remove the “lastdate1” column by clicking on “home -> choose columns -> choose columns”:

To get the last date for 1 specific value, for instance, status = close, only at the step 2, in the formula bar, before the “red” part, put: Table.SelectRows(_, each [argument]="value")

For 2 specific values, for instance, status = open and status = close, from the step 2, I need to have 2 “lastdate” columns:

In the formula bar, before the “red” part, put the formula of the 1 specific value:

NOTE: in my example, “lastdate1” is “open” and “lastdate2” is “close”

Click on the “expand” icon of the “groupby” column to select all columns except the “number” one:

Click on the “expand” icon of the “date1” column to select any 2 dates:

In the formula bar, change the “red” part by the 2 new columns:

Once done, I can keep like this or remove the “lastdate” columns as in step 6:

Now if I want to get the last date between 2 columns, in my example, date1 and date2, in the “group by” popup, configure like that:

Click on “add column -> custom column”:

From here, there are 2 options, one without date and one with date.

• Option 1 without date

Put this formula: if [argument3] = null then [argument1]=[argument2] else [argument3]=[argument4]

Filter the “custom” column like that:

• Option 2 with date

Put this formula: if [argument3] = null then [argument1] else [argument3]

Click again to “add column -> custom column” to put this formula:

if [argument4] = null then [argument2] else [argument4]

Filter the “custom.1” column to unselect whatever you want:

In the formula bar, change the “red” part by: [argument5]=[argument6]