Forum Discussion
Solved
Remove Duplicate group by another column
Hi guys
I have one table as below in Power Querry
Date Product ID Campaign ID
01.01.2023 A 11
01.01.2023 A 10
01.02.2023 A 12
And I would like to remove the duplicate of Product ID by Date only (regardless of Campaign ID)
Results:
Date Product ID
01.01.2023 A
01.02.2023 A
How could I make it with power querry? Could you please help? Thank you in advance
Hello - you can do it by grouping the table, like this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUAyIjAyNjJR0lRyA2NFSK1cEmbgAXN0IRN1KKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Product ID" = _t, #"Campaign ID" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Product ID", type text}, {"Campaign ID", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Date", "Product ID"}, {{"Data", each _, type table [Date=nullable date, Product ID=nullable text, Campaign ID=nullable number]}}), #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Date", "Product ID"}) in #"Removed Other Columns"
3 Replies
Hello - you can do it by grouping the table, like this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUAyIjAyNjJR0lRyA2NFSK1cEmbgAXN0IRN1KKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Product ID" = _t, #"Campaign ID" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Product ID", type text}, {"Campaign ID", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Date", "Product ID"}, {{"Data", each _, type table [Date=nullable date, Product ID=nullable text, Campaign ID=nullable number]}}), #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Date", "Product ID"}) in #"Removed Other Columns"Thank jennratten . It works with your solution. Thank you a lot!!!
You're welcome!
