Forum Discussion
KieuVuong
3 years agoFrequent Visitor
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 ...
- 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"
jennratten
3 years agoSuper User
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"