Forum Discussion
Help needed: Remove duplicates - keep newest data
Hi All,
I have the following issue:
I have a folder where excel files with raw data are being uploaded. Each file has data which is included also the other files - Here how it works:
FileNumberOne29.03.2020 - has data for the period 24.03 - 28.03
FileNumberOne30.03.2020 - has data for the period 25.03 - 29.03
and so on...
Here a snapshot with dummy data to illustrate how it looks like:
First file has:
Second file has:
When , in PowerQuery, I select the columns in Yellow(all at once) and then click Remove duplicates and click close and apply I will get the following result:
All good here.. Positive feedback value update to 2 from 1 for the first 2 rows from 28.03. HOWEVER - the row in orange was a part of the file for 28.3, but not in the file for 29.3. Therefore this row is unique and is being added , however what I need is to make sure that if a row has a duplicate based on the columns in yellow it should be removed and keep only the newest data.
If a row is present in a previous version, but not in the newest one it should be removed (in this case the orange row) - please help me!!!!
Thanks!
Best regards,
Ivan
Hi IPGeorgiev
please check the enclosed file.
I've faked the "from-folder"-experience so that I could use the combine-binaries-technique.
I've extracted the content from the newest file first then filtered out the first file from the from-folder-table before expanding its "Content"-column out. That generated all the other queries that you see in the file.
17 Replies
Hi IPGeorgiev
not sure I fullly understand your request, but please de-select the Date-column from the yellow columns and check if that delivers what you want.
If not, please exactly explain what you mean with your last sentence. "If a row is present in a previous version, but not in the newest one it should be removed (in this case the orange row)... " If that's the case, you just have to keep the newest date.
If you have to sort your data before removing dups, make sure to use a buffer to be on the safe side: https://community.powerbi.com/t5/Community-Blog/Bug-warning-for-Table-Sort-and-removing-duplicates-in-Power/ba-p/810390
Hi ImkeF ,
I really appreciate your support on this one! thanks a lot!
1. I have tried with time date - removing it makes thing worse actually.
"If a row is present in a previous version, but not in the newest one it should be removed (in this case the orange row)... " - I mean the following:
As a data source I have selected a "FOLDER" in this Folder files with raw data are uploaded daily - each file has 7 days rolling data - meaning the data for the previous 6 days should be overwritten and the completely new data is for the previous day only. HOWEVER there might be changes for the last 6 days as well thats why when a new file is uploaded to the Folder we need to get the data from the new file and completely overwrite the data for the last 6 days prior to the previous day:
For example:
In file called - RAW1 - we have data for 25.03 - 31.03 - file uploaded in the folder on 01.04
In file called - RAW2 - we have data fore 26.03 -01.04 - file uplaoded in the folder on 02.04
In file called - RAW3 - we have data for 27.03 - 02.04 - file uploaded in the folder on 03.04
So in this case the data for 25.03 will remain from RAW1
The data for 26.03 will remain from the file RAW2.
The data for the period 27.03-02.04 will remain from RAW3
and so on..
No in my case I have one row in one of the files for a specific date which , when receiving the newer file is no longer there and this way it remain since it has no duplicate (the orange row in my first example).
Is there a way to achieve that what I am aiming for?
Thanks!
Best regards,
Ivan
