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])
I cannot use Count and Count(Distinct) in Power Query.
I'm not sure how to further clarify what I need as I think it is pretty obvious.
| PK | Duplicate Flag |
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
| 3 | 2 |
| 4 | 1 |
I need to add the Duplicate Flag column in Power Query. Each record that has a count more than once is a duplicate. I'm going to apply a filter on the Duplicate Flag to remove these records.
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.