Forum Discussion
How to identify duplicate column records in Power Query
Hi Quiny_Harl, don't be affraid of using Group By - it is one of my favourite functions.
You can achieve what you need in single step.
This code will preserve all your exicting columns and add new [Duplicate Flag] at the end.
Add this code as new step, but replace Source with your previous step reference and "PK" with your column name where you want to check for duplicates
Table.Combine(Table.Group(Source, {"PK"}, {{"All", each Table.AddColumn(_, "Duplicate Flag", (x)=> Table.RowCount(_), Int64.Type), type table}})[All])
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxkikiVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PK = _t]),
#"Added Custom" = Table.AddColumn(Source, "Duplicate Flag", (k)=> List.Count(List.Select(Source[PK], each _ = k[PK] )))
in
#"Added Custom"
You can throw a List.Buffer into the mix if you need better performance.
Thank you for trying to help me but I don't understand how to use this code.
How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.