Forum Discussion
Help needed: Remove duplicates - keep newest data
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.
Sorry IPGeorgiev
but this is now clear to me.
Please post sample data with before and after that match exactly what you're after and that contain all relevant cases.
Make sure to follow these guidelines: https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Hi ImkeF ,
thanks a lot for you time!
The data below is an example - not real data as the real is confidential, however this data fully represents the issue:
So the data is being uplaoded in .xlsx format in a folder called Test/DailyQuality
the Power BI report is getting data from this folder.
On 31.03.2020 the file with data up to 30.03.2020 is uploaded. Here we will pretend like we have data for just 1 employee, where in reallity there are hundreds of employees. The sheet is called 31.03.2020
Then on the next day (01.04.2020) we upload also the newest file with data up to 31.03.2020 - Sheet called 01.04.2020.
When marking some of the columns (Those in Yellow in the previous post) and I click on remove duplicates I end up with the result in the sheet "Current Output"
Where the desired output is in the sheet DesiredOutput
Link to the file: https://drive.google.com/file/d/1413E47UNahdpLaPLa7fCdPvopp-zB7Cb/view?usp=sharing
Many thanks in advance!!
Best regards,
Ivan
Thanks, think I understand now.
Before expanding the file contents, I'd "split up the From-folder-table":
1) Deselect latest file and select only those rows where the Date.From([Upload Date]) - Date.From([Time Date]) = #duration(7,0,0,0).
Alternatively you could determine the earliest date from each file and keep just those rows.
2) Append 1 to the complete content of the latest file. With this method, you shouldn't have to remove duplicates at all, if my understanding is correct.
Hi ImkeF ,
Many many thanks for the support! Solving this issue would really mean a lot to me!!
I know that this might be a stupid question, but would it be possible to be more specific on how to execute those two steps, I am afraid that my knowledge doesnt let me do it 😞
Before expanding the file content I have the following columns - Content, Source.Name, Extension, Date accessed, Date Modified, Date Created, Attributes, Folder path, Transform File, Transform File (2)
Many thanks in advance!
P.S.
Alternatively you could determine the earliest date from each file and keep just those rows.
Is it possible to - keep only the earliest date from all files, but the last one (last created)? This will also surely solve this problem.
Thanks a lot , I really appreciate your time and support!
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.