Forum Discussion
Helper VNeed help on removing duplicates records from columns
Helper VThanks for your quick response.
This is my code
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(Source[Scripts]),
Custom1 = Table.FromColumns( {DistinctScripts} & Table.ToColumns(Table.RemoveColumns(Source, {"Scripts"})), Value.Type(Source) )
in
Custom1
I am getting mention below error
Community ChampionYou have to refer previous step in DistinctScripts step:
Use this:
DistinctScripts = List.Distinct(#"Removed Other Columns"[Scripts]),
- KuntalSingh
Now 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 - dufoq3
Ah, sorry - replace also every Source with #"Removed Other Columns" at last step.
- ahadkarimi
Solution Specialist
Try 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 - 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
