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 la...
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"
ImkeF
Community Champion
Community ChampionHi 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"