Remove duplicate in dataset

[Anonymous]

Hi,

based on below  table I want to remove duplicate  for a new  date  from table 2, I used calculation table to generate table 2, but some case in the end date show twoic,  how we can remove duplicated records.

[jdbuchanan71]

If you put a SUMMARIZE around your code that generates the table and summarize by your three columns it will return just the unique sets.

TestTable = 
SUMMARIZE(
    DATATABLE("start",DATETIME,"end",DATETIME,"new date",DATETIME,
    {
        {"1/1/2019","5/1/02019","1/1/2019"},
        {"1/1/2019","5/1/02019","2/1/2019"},
        {"1/1/2019","5/1/02019","3/1/2019"},
        {"1/1/2019","5/1/02019","4/1/2019"},
        {"1/1/2019","5/1/02019","5/1/2019"},
        {"1/1/2019","5/1/02019","5/1/2019"}
    })
,[start],[end],[new date])

[Anonymous]

@jdbuchanan71 

thanks for your replay.

kindly note that I want to remove duplicate based on composite kay, mean I want to use three fields as a kay

is your above solution do that?

[jdbuchanan71]

It will, yes.  Summarizing over the three columns will give you one row for each unique set.

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

To create a calculated table as below.

Table = 
VAR a =
    CALENDAR ( MAX ( 'Table 1'[start] ), MAX ( 'Table 1'[end] ) )
RETURN
    ADDCOLUMNS (
        a,
        "end", MAX ( 'Table 1'[end] ),
        "start", MAX ( 'Table 1'[start] )
    )