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:0...
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

     

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 

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