Forum Discussion

KieuVuong's avatar
KieuVuong
Frequent Visitor
3 years ago
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   ...
  • jennratten's avatar
    3 years ago

    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"