Remove Duplicate group by another column

[KieuVuong]

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           

[KieuVuong]

Thank @jennratten . It works with your solution. Thank you a lot!!!

[jennratten]

You're welcome!

[jennratten · Accepted solution]

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"