Forum Discussion
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...
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
Copy your Excel data and paste it in your reply to this message so we can use it to test the solution.
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 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:
Is this ok?
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.
