Forum Discussion

Tinus1905's avatar
Tinus1905
Resolver I
2 years ago
Solved

powerbi remove newest time duplicates

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?

 

 

 

Need Help
  • AlienSx's avatar
    AlienSx
    2 years ago

    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

     

12 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    2 years ago

    Tinus1905 

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


    • Tinus1905's avatar
      Tinus1905
      Resolver I
      2 years ago
      Date addedWorkdateNameStarttimeendtimetotal changed by managerNamecodeFactory
      25-10-2021 13:4125-10-2023Peter09:0017:0008:00PRBase 1
      25-10-2021 17:0525-10-2023Peter09:0017:0009:30PRBase 1
      25-10-2021 17:0523-10-2023Harry09:0017:0008:00HYStation 1
      26-10-2021 13:4126-10-2023Peter09:0017:0008:00PRBase 1
      27-10-2021 13:4126-10-2023Harry12:0017:0005:00HYStation 1
      27-10-2021 13:4126-10-2023Harry18:0022:0004:00HYStation 1
      27-10-2021 16:4526-10-2023Harry18:0022:0004:00HYStation 1
      27-10-2021 17:0527-10-2023Peter09:0017:0008:00PRBase 1
              
              
      25-10-2021 17:0525-10-2023Peter09:0017:0009:30PRBase 1
      25-10-2021 17:0523-10-2023Harry09:0017:0008:00HYStation 1
      26-10-2021 13:4126-10-2023Peter09:0017:0008:00PRBase 1
      27-10-2021 13:4126-10-2023Harry12:0017:0005:00HYStation 1
      27-10-2021 16:4526-10-2023Harry18:0022:0004:00HYStation 1
      27-10-2021 17:0527-10-2023Peter09:0017:0008:00PRBase 1
      • Fowmy's avatar
        Fowmy
        Super User
        2 years ago

        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's avatar
        Fowmy
        Super User
        2 years ago

        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.

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    2 years ago

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