SOLVED

Removing duplicates in PowerQuery

%3CLINGO-SUB%20id%3D%22lingo-sub-1500803%22%20slang%3D%22en-US%22%3ERemoving%20duplicates%20in%20PowerQuery%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1500803%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20guys!%26nbsp%3B%3C%2FP%3E%3CP%3EBefore%20I%20start%20with%20my%20question%2C%20please%2C%20take%20a%20look%20the%20pictures%20that%20I%20attached.%3C%2FP%3E%3CP%3EMy%20source%20is%20folder%20X%20which%20contains%203%20files%20(but%20also%20can%20contain%20x%20files%20and%20problem%20is%20still%20there).%20What%20I%20do%3A%20I%20import%20data%20in%20Power%20Query%2C%20delete%20column%20with%20file%20name%2C%20sort%20them%20by%20date%20from%20newest%20to%20oldest.%20Then%20I%20want%20to%20know%20what%20is%20last%20status%20of%20item%20that%20is%20bar%20coded.%20When%20I%20remove%20duplicates%20(bar%20Code%20column)%20I%20only%20get%20status%20from%20the%201st%20file%2C%20but%20I%20want%20to%20get%20status%20with%20newest%20date%20whatever%20is%20position%20of%20file%20in%20folder.%3C%2FP%3E%3CP%3EWhat%20is%20the%20trick%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1500803%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1502152%22%20slang%3D%22en-US%22%3ERe%3A%20Removing%20duplicates%20in%20PowerQuery%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1502152%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F713768%22%20target%3D%22_blank%22%3E%40Zarko_Tripunovic%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERemove%20Duplicates%20keeps%20first%20of%20duplicated%20rows%20in%20order%20of%20how%20you%20sorted%20records%20before.%20However%2C%20to%20ensure%20it%20works%20correctly%20before%20removing%20duplicates%20you%20need%20to%20fix%20the%20table%20in%20memory.%20You%20may%20wrap%20it%20by%20Table.Buffer%2C%20or%20easier%20to%20add%20Index%20column-%26gt%3Bselect%20column(s)%20for%20which%20remove%20duplicates-%26gt%3Bremove%20them-%26gt%3Bremove%20Index%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1502545%22%20slang%3D%22en-US%22%3ERe%3A%20Removing%20duplicates%20in%20PowerQuery%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1502545%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThank%20You%20again%20for%20Your%20help.%20I%20used%20Table.Buffer%20and%20it%20works%20perfectly%20now.%20After%20You%20advised%20me%20to%20use%20this%2C%20I%20wasn't%20sure%20how%20to%20use%20it%20so%20I%20found%20it%20on%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fexceleratorbi.com.au%2Fremove-duplicates-keep-last-record-power-query%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceleratorbi.com.au%2Fremove-duplicates-keep-last-record-power-query%2F%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi guys! 

Before I start with my question, please, take a look the pictures that I attached.

My source is folder X which contains 3 files (but also can contain x files and problem is still there). What I do: I import data in Power Query, delete column with file name, sort them by date from newest to oldest. Then I want to know what is last status of item that is bar coded. When I remove duplicates (bar Code column) I only get status from the 1st file, but I want to get status with newest date whatever is position of file in folder.

What is the trick?

 

 

3 Replies
Highlighted
Best Response confirmed by Zarko_Tripunovic (Occasional Contributor)
Solution

@Zarko_Tripunovic 

Remove Duplicates keeps first of duplicated rows in order of how you sorted records before. However, to ensure it works correctly before removing duplicates you need to fix the table in memory. You may wrap it by Table.Buffer, or easier to add Index column->select column(s) for which remove duplicates->remove them->remove Index column.

Highlighted

@Sergei Baklan Thank You again for Your help. I used Table.Buffer and it works perfectly now. After You advised me to use this, I wasn't sure how to use it so I found it on https://exceleratorbi.com.au/remove-duplicates-keep-last-record-power-query/ 

Highlighted

@Zarko_Tripunovic 

Sorry didn't explain Table.Buffer() in more details. But again, simplest way is to Add index->Sort->Remove index

Anyway, glad to know you sorted the issue out.