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
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

       

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

        You have to refer previous step in DistinctScripts step:

         

        Use this:

        DistinctScripts = List.Distinct(#"Removed Other Columns"[Scripts]),