Forum Discussion
Remove Duplicates - filter/unfilter - before pivot
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 |
Hi Anonymous ,
you have to tackle this a bit differntly in Power Query:
1) sort on StatusHistory descending and buffer the result2) 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"
1 Reply
Hi Anonymous ,
you have to tackle this a bit differntly in Power Query:
1) sort on StatusHistory descending and buffer the result2) 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"