powerbi remove newest time duplicates

[Tinus1905]

Hi,

In PowerBi I have a table with duplicate values. I want to remove the duplicates so the oldest would be deleted. 

You can see that Peter worked on 25-10-2023 13:41 with working hours of 08:00. The manager changed this to 09:30 on 25-10-2023 17:05. The first one must be deleted. Harry worked on 26-10-2023 but it is listed twice (27-10-2023 13:41 and 27-10-2023 16:45). The oldest date and time must be deleted. 

Sometimes a agent worked twice a day. Harry worked on 26-10-2023 from 12:00 - 17:00 and from 18:00 - 22:00. Now nothing must be deleted. 

Is it possible to achieve this?

[Syndicate_Admin]

This outcomes in an elevated degree of authenticity with exact light dispersion and reflections.

[Fowmy]

@Tinus1905 

Copy your Excel data and paste it in your reply to this message so we can use it to test the solution.

[Tinus1905]

Is this ok?

[Fowmy]

@Tinus1905 

No, you have pasted a picture. 

Sharing a dummy Power BI file representing your scenario would be beneficial. You can save the Power BI file or the Excel file on Google Drive or any other cloud storage platform and provide the link here. Kindly ensure that permission is granted to open the file.

[Tinus1905]

https://drive.google.com/file/d/10K9C5KanD--RA7BB-YFzYxRub41tpWhx/view?usp=drive_link 

https://docs.google.com/spreadsheets/d/15WYUSTdnq0Ieh0IsE6ttswxtjOUdr-gc/edit?usp=drive_link&ouid=10... 

[AlienSx]

@Tinus1905 requests access to your file... Group by name, workday, starttime (maybe + endtime). Sort each group by  date added and take either first or last row (Table.FirstN or Table.LastN) depending on sorting order.

Table.Group(tbl_name, {"Workdate", ..., "endtime"}, {"groups", each Table.LastN(Table.Sort(_, "Date added"), 1) and then combine groups into new table: Table.Combine(step_above[groups])  

[Tinus1905]

@AlienSx you can access the file.

I don't quite understand what you mean, but probably when I see it in the file it will become clear. 

[AlienSx · Accepted solution]

@Tinus1905 open Blad1 in Advanced Editor and replace everything with the following: 

let
    Source = Excel.Workbook(File.Contents("\\dc01\Users\martijnb\Desktop\Hours 2.xlsx"), null, true),
    Blad1_Sheet = Source{[Item="Blad1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Blad1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date added", type datetime}, {"Workdate", type date}, {"Name", type text}, {"Starttime", type time}, {"endtime", type time}, {"total changed by manager", type time}, {"Namecode", type text}, {"Factory", type text}}),
    g = Table.Group(#"Changed Type", {"Workdate", "Name", "Starttime"}, {{"all", each Table.LastN(Table.Sort(_, "Workdate"), 1)}}),
    combine = Table.Combine(g[all])
in
    combine

[Tinus1905]

@Fowmy and @AlienSx thanks for the help. It works great!! 

[Tinus1905]

Date added	Workdate	Name	Starttime	endtime	total changed by manager	Namecode	Factory
25-10-2021 13:41	25-10-2023	Peter	09:00	17:00	08:00	PR	Base 1
25-10-2021 17:05	25-10-2023	Peter	09:00	17:00	09:30	PR	Base 1
25-10-2021 17:05	23-10-2023	Harry	09:00	17:00	08:00	HY	Station 1
26-10-2021 13:41	26-10-2023	Peter	09:00	17:00	08:00	PR	Base 1
27-10-2021 13:41	26-10-2023	Harry	12:00	17:00	05:00	HY	Station 1
27-10-2021 13:41	26-10-2023	Harry	18:00	22:00	04:00	HY	Station 1
27-10-2021 16:45	26-10-2023	Harry	18:00	22:00	04:00	HY	Station 1
27-10-2021 17:05	27-10-2023	Peter	09:00	17:00	08:00	PR	Base 1
25-10-2021 17:05	25-10-2023	Peter	09:00	17:00	09:30	PR	Base 1
25-10-2021 17:05	23-10-2023	Harry	09:00	17:00	08:00	HY	Station 1
26-10-2021 13:41	26-10-2023	Peter	09:00	17:00	08:00	PR	Base 1
27-10-2021 13:41	26-10-2023	Harry	12:00	17:00	05:00	HY	Station 1
27-10-2021 16:45	26-10-2023	Harry	18:00	22:00	04:00	HY	Station 1
27-10-2021 17:05	27-10-2023	Peter	09:00	17:00	08:00	PR	Base 1

[Fowmy]

@Tinus1905 

Create a blank Query, go to the Advanced Editor, clear the existing code, and paste the codes give below and follow the steps. Check the last two steps where I used custom code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZHBCsIwDIZfpfTsoO3WTXcUkZ1k6EFkeIhrUdGtUHPZ29u5TURRinrpn6bJx5+0KOgMUBNQSis6omtjT8olXLiAqpUVgkU83mJdqz5Cg3Am5QHqvVZk15AKathr2/eVRrVVcyjR2IZuRwUVMuAsEExwwsM04u75ngrdJdd4a2eTlDGnPOmUjTvNl+6YwkUT/sJzpdKbN0lDX174wMvAukne+8s23bLwaOoBGb+OHP8ycvKZN1jk4okn31v0RfaWRI9mkRcyTiP5Z+TwN8m3i9xeAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t, #"(blank).7" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}, {"(blank).5", type text}, {"(blank).6", type text}, {"(blank).7", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date added", type text}, {"Workdate", type text}, {"Name", type text}, {"Starttime", type time}, {"endtime", type time}, {"total changed by manager", type time}, {"Namecode", type text}, {"Factory", type text}}),
    NoDups = Table.Distinct(#"Changed Type1", {"Workdate", "Name", "Starttime", "endtime", "total changed by manager"}),
    #"Grouped Rows" = Table.Combine(Table.Group( NoDups, {"Workdate", "Name", "Starttime", "endtime"}, {{"Change", each let maxtime = List.Max(_[total changed by manager]) in Table.SelectRows(_,each [total changed by manager]= maxtime) }})[Change])
in
    #"Grouped Rows"

Result:

[Fowmy]

@Tinus1905 

Please confirm if my solution worked for you? 
Feedback will be helful.