Forum Discussion
KuntalSingh
Helper V
Helper VNeed help on removing duplicates records from columns
I want to remove duplicates records from column A (Script) and Column D (Document Number) Scripts CompanyCode FiscalYear DocumentNumber S(0-25)-1 US29 2024 5101197126 S(0-25)-1 US...
ahadkarimi
Solution Specialist
Solution SpecialistTry this code and let me know if you encounter any issues.
let
Source = Excel.Workbook(File.Contents("C:\Users\KUNTALSINGH\Box\PepsiCo - NA Process Session\ACL Output file.xlsx"), null, true),
Sheet1 = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
PromotedHeaders = Table.PromoteHeaders(Sheet1, [PromoteAllScalars=true]),
RemoveDuplicatesScripts = Table.Distinct(PromotedHeaders, {"Scripts"}),
RemoveDuplicatesDocumentNumber = Table.Distinct(RemoveDuplicatesScripts, {"DocumentNumber"})
in
RemoveDuplicatesDocumentNumber
KuntalSinghHelper V
Helper VThanks for reply ask is
want output as and rules are
1. Concatenate Document Number, Year and Company Code in Input sheet - Copy and Paste in New sheet
2. Remove duplicates from Column A (Scripts) and transpose it in New Sheet
3. Remove duplicates from DocumentNumber Column and paste it in new sheet
4. Apply CountIF formula Based on Scripts
if counts matches in all the scripts then we can keep only the first one and flag other scripts in the Input file.
| CC | S(0-25)-1 | S(25-60)-1 | S(25-60)-2 | S(25-60)-3 | S(25-60)-4 | S(60-87)-1 | S(60-87)-2 | S(60-87)-3 | S(60-87)-4 | S(60-87)-5 | S(87-100)-1 | S(87-100)-2 | S(87-100)-3 | S(87-100)-4 | S(87-100)-5 | |||
| 51011971262024US29 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | ||
| 51000170802023US29 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | ||
| 51014135282024US28 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | ||
| 51014915912024US28 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | ||
| 51015660152024US28 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | ||
| 51015675162024US28 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | ||
| 51017525212024US28 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | ||
| 51018740112024US28 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |