Remove Duplicates - filter/unfilter - before pivot

[Anonymous]

I have looked everywhere and cannot find an answer to this.  Please Help!

We track proposals through an approval process, but sometimes a proposal gets double approved and I need to only take the latest approval for that status.

When I was in excel, I would just filter on the status, sort by date, and then remove duplicates. I could then UNfilter the table and move on with the pivot.  The pivot cannot have more than one date per status per proposal.

In Power BI Query Editor, I can't UNfilter after I remove the duplicates, so I'm stuck on how to get these outliers removed.

Below is a sample of my data. The red row is the only row in this example set that needs to be removed.

_id	statusHistory.createdAt	statusHistory.status	proposals.title
5dc33a363bafff4770b0458f	10/2/2019 4:00:00 AM	IT/OT Team	Operating Budget Request Application
5dc33a363bafff4770b0458f	2020-01-10T15:02:13.3660000	Executive Approval	Operating Budget Request Application
5dc33a363bafff4770b0458f	2021-01-28T19:28:42.0190000	Approved	Operating Budget Request Application
5dc33a363bafff4770b0458f	4/3/2019 7:58:00 PM	Saved	Operating Budget Request Application
5dc33a363bafff4770b0458f	7/10/2019 4:00:00 AM	Business Case	Operating Budget Request Application
5dc33a363bafff4770b0448a	2020-06-09T13:55:27.9050000	Sponsor Approval	Outdoor Advertising System
5dc33a363bafff4770b0448a	2020-06-09T13:59:51.3850000	Joint Directors Approval	Outdoor Advertising System
5dc33a363bafff4770b0448a	2020-06-09T14:00:08.7670000	Business Case	Outdoor Advertising System
5dc33a363bafff4770b0448a	2020-06-09T14:04:01.0320000	IT/OT Team	Outdoor Advertising System
5dc33a363bafff4770b0448a	2020-06-09T14:04:40.7330000	Executive Approval	Outdoor Advertising System
5dc33a363bafff4770b0448a	2020-06-09T14:04:56.7810000	Quarterly Release Cycle (QRC) Approval	Outdoor Advertising System
5dc33a363bafff4770b0448a	2020-06-09T14:09:01.4600000	Approved	Outdoor Advertising System
5dc33a363bafff4770b0448a	2020-06-09T13:59:08.7900000	Functional Coordinator Approval	Outdoor Advertising System
5dc33a363bafff4770b0448a	9/14/2018 12:08:00 PM	Saved	Outdoor Advertising System
5dc33a363bafff4770b045b8	2020-01-28T18:53:52.3750000	Quarterly Release Cycle (QRC) Approval	PAS GIS Routing Update
5dc33a363bafff4770b045b8	2020-02-03T17:43:59.9180000	Approved	PAS GIS Routing Update
5dc33a363bafff4770b045b8	5/10/2019 11:15:00 PM	Saved	PAS GIS Routing Update
5f98702779817e002858834d	2020-11-17T21:33:59.7790000	Functional Coordinator Approval	One Stop Permitting (OSP) Enhancements
5f98702779817e002858834d	2021-01-07T16:10:10.8000000	Functional Coordinator Approval	One Stop Permitting (OSP) Enhancements
5f98702779817e002858834d	2020-10-27T19:08:23.6150000	Saved	One Stop Permitting (OSP) Enhancements
5f98702779817e002858834d	2020-12-16T11:48:46.6690000	Sponsor Approval	One Stop Permitting (OSP) Enhancements
5f98702779817e002858834d	2021-01-20T10:44:26.5310000	Joint Directors Approval	One Stop Permitting (OSP) Enhancements
5f98702779817e002858834d	2021-02-17T17:57:14.6710000	Business Case	One Stop Permitting (OSP) Enhancements

[ImkeF · Accepted solution]

Hi @Anonymous ,

you have to tackle this a bit differntly in Power Query:

1) sort on StatusHistory descending and buffer the result

2) check the other columns and remove duplicates

If you paste this code into the advanced editor you should be able to follow the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xZbbTttAEIZfZZUrkMhmZs/eO0hpRaWKELtXiAtjb6ilxE7tNSpvw7PwZF2bBHFQo0BdVfKN19J8s//8M+PLy5HMM85Trvh1ulgshNZwDUKaxehohDABNmGAEQFhAcLRWTI5T0ji0lV4OV+7OvVFeUNO2vzGeTJ3P1vXeHK8Xi+LLHyqytHV0U4GAwZjwDFCgtICs8gpVwqgx53+clnri1vXhayr23Q5HBY7LDMJRpYZKxgN99xgH2EuHwgGYgL8UccAkyYcxemA4fUkVOpFmU7apihd05Bp2ri/xgiTPlVKjSFKkFspLdM0ArmRLF5XZVPVL+rU+rzqjvJbV/ui6fDxXePd6p2ooBlSbraor1VRevKpqF3mq7oZHtmJaMFQrfQG+UbPoTjhQQqcbTgv22s4iACqOd/ZVMPBpKLa4AZ20aa1d/XyLlhu6YJ6ZHqXLR05uJhPD/9JAlEnqegnyOteHs6QnTuiLeJzW2ZdB6VLMg3x86JM/VCtEE1QdL1tCLJADc4ks2/PB8gHIstr82zydiPQWBmuxSjX8r11mx3H5MtZTOZV2w+Y7+s89W4/OBsDT1Bb0WlKIzRva/ax8CCfRiLjFuUzwXYFXERGA9M6MqgdADPSGC7ybb4Y1pROGFre56t1tL8BSkdiX63JzNWrwvfkg/N4dkhOyx9pmbmVK33zcP9wv0cm/eYCnaCyCOGhvW7/I5OgCYyZ7nZo8CbjVOHTQtgadGAeG6NKEK0IO1tRpaIdC2hw0Vn4SwErhGWKSo57LKSBU2CdA0PHSG1RUKXxTwvqHdyr3w==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [_id = _t, statusHistory.createdAt = _t, statusHistory.status = _t, proposals.title = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"_id", type text}, {"statusHistory.createdAt", type text}, {"statusHistory.status", type text}, {"proposals.title", type text}}),
    #"Sorted Rows" = Table.Buffer( Table.Sort(#"Changed Type",{{"statusHistory.createdAt", Order.Descending}}) ),
    #"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"_id", "statusHistory.status", "proposals.title"})
in
    #"Removed Duplicates"