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

     

AlienSx's avatar
AlienSx
Super User
2 years ago

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's avatar
    Tinus1905
    Resolver I
    2 years ago

    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's avatar
      AlienSx
      Super User
      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

       

      • Tinus1905's avatar
        Tinus1905
        Resolver I
        2 years ago

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