Forum Discussion

KuntalSingh's avatar
KuntalSingh
Icon for Helper V rankHelper V
2 years ago

Need 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...
Need Help
KuntalSingh's avatar
KuntalSingh
Icon for Helper V rankHelper V
2 years ago

Hi I want to remove duplicates from A column without impaction on other column

dufoq3's avatar
dufoq3
Icon for Community Champion rankCommunity Champion
2 years ago

What do you exactly mean "without impaction"? Provide expected result based on sample data please.

  • KuntalSingh's avatar
    KuntalSingh
    Icon for Helper V rankHelper V
    2 years ago

    I want output as mention below

     

    ScriptsCompanyCodeFiscalYearDocumentNumber
    S(0-25)-1US2920245101197126
    S(25-60)-1US2920235100017080
    S(25-60)-2US2820245101413528
    S(25-60)-3US2820245101491591
    S(25-60)-4US2820245101566015
    S(60-87)-1US2820245101567516
    S(60-87)-2US2820245101752521
    S(60-87)-3US2820245101874011
    S(60-87)-4US2920245101149040
    S(60-87)-5US2920245101984655
    S(87-100)-1US2820245101413527
    S(87-100)-2US2820245101575516
    S(87-100)-3100020245135751891
    S(87-100)-4100020245135751741
    S(87-100)-5100020235132476310
     100020245135737969
     100020245135738340
     100020245135513389
     US2820245100822034
     US2820245101126254
     US2820245101239148
     US2820245101580014
     100020235133914906
     100020245135751575
     100020235133931734
     100020245135737845
     100020245135726814
     100020245135726814
     US2820245100826506
     US2820245100961501
     100020245134521776
     100020245134521776
     100020245135336621
     100020245135738154
     US2920245100937885
     US2920245101564525
     US2920245101111176
     US2820245100965506
     US2820245101205008
     US2920245101370510
     US2920245101863010
     US2920245100864027
     US2920245100944811
     US2920245100966594
     US2920245101044675
     US2920245101347670
     US2920245101626518
     US2820245100933537
     US2820245101691527
     US2820245100924846
     US2820245101526515
     US2820245100905981
     US2820245101331567
     US2820245100935615
     US2820245102247526
     US2820245100905980
     US2820245101008552
     100020245135696691
     100020245135696691
     US2820245100905979
     US2820245101510053
     US2820245100822035
     US2820245101838516
     US2820245101150544
     US2820245102247525
     US2820245101410515
     US2820245101572028
     US2820245101580055
     US2820245101788006
     US2820245100909529
     US2820245101166021
     100020245135612369
     100020245135737801
     100020245135737802
     US2820245101098580
     US2820245101118052
     US2820245101455522
     US2920245100978139
     US2920245101018840
     US2920245101253032
     US2920245101514525
     US2920245100944810
     US2920245101227037
     US2920245101239571
     US2920245101525527
     US2820245100831045
     US2820245101525527
     US2820245100895316
     US2820245101036100
     US2820245102264500
     US2820245100905191
     US2920245100942062
     US2920245101100656
     US2920245101525526
     US2920245101745551
     US2920245100921753
     US2920245100979147
     US2920245101125635
     US2920245101488517
     US2920245100990601
     US2920245101100655
     US2920245101505529
     US2920245101971721
     US2920245100903653
     US2920245101148170
     US2920245101760016
     US2920245102108092
     US2920245101239533
     US2920245101514524
     US2920245101572121
     US2920245102108093
     US2920245100952867
     US2920245101147145
     US2920245101507031
     US2920245101817530
     100020245135670369
     100020245135670369
     US2820245100947882
     US2820245101020581
     US2920245100966171
     US2920245101749023
     US2920245101119120
     US2920245101232192
     US2920245100977682
     US2920245101741563
     US2820245100877090
     US2820245101177617
     US2820245100993628
     US2820245101315626
     US2820245100942417
     US2820245101072040
     US2820245100942418
     US2820245101124138
     US2920241700262501
     US2920241700290000
     100020235132526004
     100020245135751831
     100020235132476310
     100020245135737969
     100020245135738340
     100020245135513389
     US2820245100822034
     US2820245101126254
     US2820245101239148
     US2820245101580014
     100020235133914906
     100020245135751575
     100020235133931734
     100020245135737845
     100020235133931737
     100020245135737848
     100020245135726814
     100020245135726814
     US2820245100826506
     US2820245100961501
     100020245135336621
     100020245135738154
     US2920245100937885
     US2920245101564525
     US2920245101111176
     US2820245100965506
     US2820245101205008
     US2920245101370510
     US2920245101863010
     US2920245100864027
     US2920245100944811
     US2920245100966594
     US2920245101044675
     US2920245101347670
     US2920245101626518
     US2820245100933537
     US2820245101691527
     US2820245100924846
     US2820245101526515
     US2820245100905981
     US2820245101331567
     US2820245100905980
     US2820245101008552
     US2820245100935615
     US2820245102247526
     US2820245100905979
     US2820245101510053
     US2820245101150544
     US2820245102247525
     US2820245100822035
     US2820245101838516
     US2820245101580055
     US2820245101788006
     US2820245101410515
     US2820245101572028
     US2820245100909529
     US2820245101166021
     US2820245101098580
     US2820245101118052
     US2820245101455522
     100020245135612369
     100020245135737801
     100020245135737802
     US2920245100978139
    • dufoq3's avatar
      dufoq3
      Icon for Community Champion rankCommunity Champion
      2 years ago

      Like this?

       

      Output based on 1st post sample data:

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZdbaiNBDEX34q8JpEGPUkm1jjBfYfa/jbntkHa7rC6L4BjMsSxdPfv7+/b1hzaxj41vn7e/XzLwJiQNb8bEPJyl3/59XoH6AxKxU1AGxrPFxmr4rAAOtsEF0HrHvxLoxmkwE+gmJpWfDm/QaCHPoWMb1FJ5JnBE61YJ5q6jV6J2e0QttnX6IfeknUkFyHEo/o70tiR/6kKleVemkk310UeNDG0lm3hpJDZfVKIQIW0FktEPYiVSdHCLCmmBBkpsvui5WxxUzSZeJZvKnsWe5ija0uZBSo91RO/ILEfdsthfydHZqFLJDX3uPtmUNEeK+SEVkkaTxl6xSS40V/LCZlRsMkDNydOswbimvZBnlS7IQbtyT6RejCUytgqJxJNESo6X2KlLgWTA3XqFxIS3OZs56c0wR0p+opZMS6Sjj70UkVjXXM+JbBE2V12qPA0aJqOSI8ZqlTz2bH6W8h4aL/voSnmxdex65F1Nl8r/ktivKOeaSoY0lSoZdJb32ubKyWxzXfiJe2GeirmfQoZElWxKwx1Sih0zudTvdz2jUkuIfD/VSjbV+nyH5DYJ/T5fF9c2SxEJjpurGTJ3hzviL3UctlGxi4f2i/lZ2FwXNpPNdaFnsrkWNkt+Zpsrn5/RGx3Hb/iGjz42ezJ6b3isN8PJdEg/of5sVQa+IEv0eNwZaCWtOACyGa0dOD1EvUN/fe34O3b3Gm0YoldWT75CJazuTlVUpRzWrtba118UDwyXDkxhhY5KWPdFEpSjyYTmRsc9+AaFWiMrwuzGdeZMgQurUUPRgTaqVlFauVjz1jVFc9fQHo/Vs0bDT/XaaQv/2Nqd1LOrSFPDrDqmwJI0R2/1EhnyyOqSxNa1Y649kU/TCvUMN7F8CiThgtNRIZnwJBRS+vURLmlEM+msj61/ItPrhEeVFCqphOhHL0XUXEYe0ZQj3k/y/S789x8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Scripts = _t, CompanyCode = _t, FiscalYear = _t, DocumentNumber = _t]),
    DistinctScripts = List.Distinct(Source[Scripts]),
    Custom1 = Table.FromColumns( {DistinctScripts} & Table.ToColumns(Table.RemoveColumns(Source, {"Scripts"})), Value.Type(Source) )
in
    Custom1
      • KuntalSingh's avatar
        KuntalSingh
        Icon for Helper V rankHelper V
        2 years ago

        Thanks 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