Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends September 15. Request your voucher.

Reply
KuntalSingh
Helper V
Helper V

Need help on removing duplicates records from columns

‎08-09-2024 02:44 AM

I want to remove duplicates records from column A (Script) and Column D (Document Number)

 

ScriptsCompanyCodeFiscalYearDocumentNumber
S(0-25)-1US2920245101197126
S(0-25)-1US2920235100017080
S(0-25)-1US2820245101413528
S(0-25)-1US2820245101491591
S(0-25)-1US2820245101566015
S(0-25)-1US2820245101567516
S(0-25)-1US2820245101752521
S(0-25)-1US2820245101874011
S(0-25)-1US2920245101149040
S(0-25)-1US2920245101984655
S(0-25)-1US2820245101413527
S(0-25)-1US2820245101575516
S(25-60)-1100020245135751891
S(25-60)-1100020245135751741
S(25-60)-1100020235132476310
S(25-60)-1100020245135737969
S(25-60)-1100020245135738340
S(25-60)-1100020245135513389
S(25-60)-1US2820245100822034
S(25-60)-1US2820245101126254
S(25-60)-1US2820245101239148
S(25-60)-1US2820245101580014
S(25-60)-1100020235133914906
S(25-60)-1100020245135751575
S(25-60)-1100020235133931734
S(25-60)-1100020245135737845
S(25-60)-1100020245135726814
S(25-60)-1100020245135726814
S(25-60)-1US2820245100826506
S(25-60)-1US2820245100961501
S(25-60)-1100020245134521776
S(25-60)-2US2820245101315626
S(25-60)-2US2820245100942417
S(25-60)-2US2820245101072040
S(25-60)-2US2820245100942418
S(25-60)-2US2820245101124138
S(25-60)-2US2920241700262501
S(25-60)-2US2920241700290000
S(25-60)-3US2820245101410515
S(25-60)-3US2820245101572028
S(25-60)-3US2920245100942062
S(25-60)-3US2920245101100656
S(25-60)-3US2920245101525526
S(25-60)-3US2920245101745551
S(25-60)-3US2920245100921753
S(25-60)-3US2920245100979147
S(25-60)-3US2920245101125635
S(25-60)-3US2920245101488517
S(25-60)-3US2820245100909529
S(25-60)-3US2820245101166021
S(25-60)-3US2820245100822035
S(25-60)-3US2820245101838516
S(25-60)-3US2920245101742535
S(25-60)-3US2920235101103533
S(25-60)-3US2920235101984002
S(25-60)-3US2820245100905979
S(25-60)-3US2820245101510053
S(25-60)-3100020245135738340
S(25-60)-3100020245135513389
S(25-60)-3US2820245100965506
S(25-60)-3US2820245101205008
S(25-60)-3US2820245100924846
S(25-60)-3US2820245101526515
S(25-60)-3US2820245100905981
S(25-60)-3US2820245101331567
S(25-60)-3US2820245100935641
S(25-60)-3US2820245101025569
S(25-60)-3US2820245100935615
S(25-60)-3US2820245102247526
S(25-60)-3US2820245100877090
S(25-60)-3US2820245101177617
S(25-60)-3US2820245100993628
S(25-60)-3US2820245101315626
S(25-60)-3US2820245100942417
S(25-60)-3US2820245101072040
S(25-60)-3US2820245100942418
S(25-60)-3US2820245101124138
S(25-60)-3US2920245100864027
S(87-100)-5US2820235501539190
S(87-100)-5US2720245100295502
S(87-100)-5US2720235100095003
S(87-100)-5US2820235500954500
S(87-100)-5US2720235100017500
S(87-100)-5US2720245100606001
S(87-100)-5US2720245100453500
S(87-100)-5US2820231900516000
S(87-100)-5US2820231900532500
S(87-100)-5US2720235100009501
S(87-100)-5US2720235100324000
S(87-100)-5US2720245100839500
S(87-100)-5US2820235101780500
S(87-100)-5100020245135714076
S(87-100)-5100020245135751697
S(87-100)-5100020245135727111
S(87-100)-5100020245135751698
S(87-100)-5100020245135704059
S(87-100)-5100020245135751750
S(87-100)-5US2920245101753072
S(87-100)-5US2920245101768506
S(87-100)-5US2920245101876001
S(60-87)-4US3020245500462638
S(60-87)-4US3020245500579546
S(60-87)-4US3020245500582076
S(60-87)-4US3020245500338540
S(60-87)-4US2820245501626564
S(60-87)-4US2820245500563394
S(60-87)-4US2820245501081482
S(60-87)-4US2820245501698726
S(60-87)-4US2820245501713008
S(60-87)-4100020245135738198
S(60-87)-4100020245135738200
S(60-87)-4US2820245501005962
S(60-87)-4US2820245501472926
S(60-87)-4US3020245501174535
Labels:
Message 1 of 15
904 Views
0
14 REPLIES 14
Gabry
Super User
Super User

‎08-09-2024 03:08 AM

Hello,

You just need to open power query, select both columns, right click and "Remove duplicates"

Message 2 of 15
872 Views
0
KuntalSingh
Helper V
Helper V
In response to Gabry

‎08-09-2024 08:34 AM

I tried to remove duplicates by selecting both column but it does't work

Message 3 of 15
834 Views
0
dufoq3
Super User
Super User
In response to KuntalSingh

‎08-09-2024 09:05 AM

Hi, it should work. Are you sure that there are not any trailing spaces in data etc? Provide screenshot of duplicates after removig duplicates please.


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Message 4 of 15
829 Views
0
KuntalSingh
Helper V
Helper V
In response to dufoq3

‎08-09-2024 09:08 AM

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

Message 5 of 15
824 Views
0
dufoq3
Super User
Super User
In response to KuntalSingh

‎08-09-2024 09:14 AM

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


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Message 6 of 15
819 Views
0
KuntalSingh
Helper V
Helper V
In response to dufoq3

‎08-09-2024 09:20 AM

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
Message 7 of 15
813 Views
0
dufoq3
Super User
Super User
In response to KuntalSingh

‎08-09-2024 09:27 AM

Like this?

 

Output based on 1st post sample data:

 

dufoq3_0-1723220801459.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Message 8 of 15
805 Views
0
KuntalSingh
Helper V
Helper V
In response to dufoq3

‎08-09-2024 10:21 PM

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

KuntalSingh_0-1723267304865.png

 

Message 9 of 15
741 Views
0
dufoq3
Super User
Super User
In response to KuntalSingh

‎08-09-2024 11:53 PM

You have to refer previous step in DistinctScripts step:

 

Use this:

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Message 10 of 15
728 Views
0
KuntalSingh
Helper V
Helper V
In response to dufoq3

‎08-10-2024 08:20 AM

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

 

KuntalSingh_0-1723303213761.png

 

Message 11 of 15
700 Views
0
ahadkarimi
Solution Specialist
Solution Specialist
In response to KuntalSingh

‎08-11-2024 01:05 PM

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

Message 14 of 15
606 Views
0
KuntalSingh
Helper V
Helper V
In response to ahadkarimi

‎08-11-2024 08:51 PM

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 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)-1S(25-60)-1S(25-60)-2S(25-60)-3S(25-60)-4S(60-87)-1S(60-87)-2S(60-87)-3S(60-87)-4S(60-87)-5S(87-100)-1S(87-100)-2S(87-100)-3S(87-100)-4S(87-100)-5 
51011971262024US29  1000000000000001
51000170802023US29  1000000000000001
51014135282024US28  1000000000000001
51014915912024US28  1000000000000001
51015660152024US28  1000000000000001
51015675162024US28  1000000000000001
51017525212024US28  1000000000000001
51018740112024US28  1000000000000001
Message 15 of 15
588 Views
0
dufoq3
Super User
Super User
In response to KuntalSingh

‎08-10-2024 08:29 AM

Ah, sorry - replace also every Source with #"Removed Other Columns" at last step.


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Message 12 of 15
696 Views
0
KuntalSingh
Helper V
Helper V
In response to dufoq3

‎08-11-2024 08:50 PM

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 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)-1S(25-60)-1S(25-60)-2S(25-60)-3S(25-60)-4S(60-87)-1S(60-87)-2S(60-87)-3S(60-87)-4S(60-87)-5S(87-100)-1S(87-100)-2S(87-100)-3S(87-100)-4S(87-100)-5 
51011971262024US29  1000000000000001
51000170802023US29  1000000000000001
51014135282024US28  1000000000000001
51014915912024US28  1000000000000001
51015660152024US28  1000000000000001
51015675162024US28  1000000000000001
51017525212024US28  1000000000000001
51018740112024US28  1000000000000001
Message 13 of 15
591 Views
0

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

View All
Top Solution Authors
View All