Forum Discussion

walker21's avatar
walker21
Frequent Visitor
3 years ago
Solved

Remove Duplicates

Hello,

I have a column called "name" and this column duplicates depending on what city they go to. I need to be able to delete the first row of the duplicates and keep the latest actions. I tried the below formula, which would give me the first date of the "name", but it will not change based on the week number of the year I choose. It always gives me the oldest record.

 

Show First Date = IF(
    CALCULATE(
        COUNTROWS(cr05d_table),
        FILTER(
            ALLEXCEPT(cr05d_table, cr05d_table[Name]),
            cr05d_table[Created On] < EARLIER(cr05d_table[Created On]))
    )
    >1,
    0,
    1
)
 
I need to show what the first date is when I filter by different weeks.
 
This is what I am hoping to achieve.

 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    Hi,

    In the Query Editor, ensure that the column is formatted as Date/Time (since the entries in that column are lft aligned, it seems that the data type of the column is text).  Thereafter write this calculated column formula

    Test = 1*(Data[created on]=calculate(min(Data[Created on])),filter(Data,Data[Created on]=earlier(Data[Created on])&&data[week number]=earlier(data[week number]))))

    Hope this helps.

2 Replies

  • Ahmedx's avatar
    Ahmedx
    Super User
    3 years ago

    Is this what you are looking for?
    you can do it in power query

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    In the Query Editor, ensure that the column is formatted as Date/Time (since the entries in that column are lft aligned, it seems that the data type of the column is text).  Thereafter write this calculated column formula

    Test = 1*(Data[created on]=calculate(min(Data[Created on])),filter(Data,Data[Created on]=earlier(Data[Created on])&&data[week number]=earlier(data[week number]))))

    Hope this helps.