Tagging required data by several conditions

[Blouey]

Hi all,

The sample data set is shown below for the problem.

I'm trying filter out a data set so that for a given TimePeriod, each Name has only their most recent ModifiedDate retained. I would also need for duplicates of the same Name, TimePeriod and ModifiedDate to be filtered out (eg. first two rows for Dan).

Any advice appreciated.
Many thanks!

Name	TimePeriod	ModifiedDate
Ash	30/01/2022 23:59	30/01/2022 11:10
Ash	30/01/2022 23:59	30/01/2022 11:10
Bob	30/01/2022 23:59	31/01/2022 4:50
Bob	30/01/2022 23:59	28/01/2022 0:55
Bob	30/01/2022 23:59	28/01/2022 0:56
Matt	30/01/2022 23:59	30/01/2022 11:57
Matt	30/01/2022 23:59	30/01/2022 11:58
Dan	30/01/2022 23:59	4/02/2022 0:09
Dan	30/01/2022 23:59	4/02/2022 0:09
Ash	6/02/2022 23:59	3/02/2022 8:00
Ash	6/02/2022 23:59	3/02/2022 8:00
Bob	6/02/2022 23:59	2/02/2022 22:45
Bob	6/02/2022 23:59	3/02/2022 22:31
Bob	6/02/2022 23:59	4/02/2022 5:05
Bob	6/02/2022 23:59	4/02/2022 5:05
Matt	6/02/2022 23:59	4/02/2022 6:11
Matt	6/02/2022 23:59	4/02/2022 6:11
Dan	6/02/2022 23:59	4/02/2022 5:49
Dan	6/02/2022 23:59	4/02/2022 5:49
Dan	6/02/2022 23:59	4/02/2022 5:49

[wdx223_Daniel]

NewStep=Table.FromRecords(Table.Group(PreviousStepName,{"Name","TimePeriod"},{"n",each Table.Max(_,"ModifiedDate")})[n])

[tamerj1 · Accepted solution]

Hi @Blouey 
You can start with a calculated column using the followinng code:

Keep? = 
VAR CurrentName = Data[Name]
VAR CurrentTimePeriod = Data[TimePeriod]
VAR CurrentModifiedDate = Data[ModifiedDate]
VAR FilteredTable1 =
    CALCULATETABLE ( 
        Data,
        Data[Name] = CurrentName,
        Data[TimePeriod] = CurrentTimePeriod,
        REMOVEFILTERS ()
    )
VAR LastModifiedDate =
    MAXX (
        FilteredTable1,
        Data[ModifiedDate]
    )
RETURN
    IF (
        CurrentModifiedDate = LastModifiedDate,
        "Yes"
    )

Then you can calculate a new table using this code:

FilteredTable = 
DISTINCT ( 
    CALCULATETABLE (
        Data,
        NOT ISBLANK ( Data[Keep?] )
    )
)

Please let me know if this answers your query.
You can find the Pbi file here https://www.dropbox.com/t/YhBedW8OGb8xrocH

[amitchandak]

@Blouey , You need to add an index column in the power query as all timestamps are the same

you can create a flag like , a new column, and use that in filter

if([index] = maxx(filter(Table, Table[Name] =earlier(Table[Name])  && Table[Modified Date] =earlier(Table[Modified Date]) ), [Index]) ,1,0)