Forum Discussion
Helper VNeed help on removing duplicates records from columns
Helper VNow I am getting another error
let
Source = Excel.Workbook(File.Contents("C:\Users\KUNTALSINGH\Box\PepsiCo - NA Process Session\ACL Output file.xlsx"), null, true),
Inputsheet_Sheet = Source{[Item="Inputsheet",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Inputsheet_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Scripts", type text}, {"Source", type text}, {"Vendor", Int64.Type}, {"VendorName", type text}, {"CompanyCode", type text}, {"FiscalYear", Int64.Type}, {"DocumentType", type text}, {"DocumentDate", type date}, {"PostingKey", Int64.Type}, {"PostingDate", type date}, {"DocumentNumber", Int64.Type}, {"Reference", type text}, {"Amountindoccurr", type number}, {"Documentcurrency", type text}, {"Amountinlocalcurrency", type number}, {"LocalCurrency", type text}, {"Clearingdate", type text}, {"Text", type text}, {"Netduedate", type text}, {"Value", type text}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Scripts", "CompanyCode", "FiscalYear", "DocumentNumber"}),
#"DistinctScripts" = List.Distinct(#"Removed Other Columns"[Scripts]),
Custom1 = Table.FromColumns( {DistinctScripts} & Table.ToColumns(Table.RemoveColumns(Source, {"Scripts"})), Value.Type(Source) )
in
Custom1
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
- KuntalSingh
Thanks 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 sheet2. Remove duplicates from Column A (Scripts) and transpose it in New Sheet3. Remove duplicates from DocumentNumber Column and paste it in new sheet4. Apply CountIF formula Based on Scriptsif 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