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.
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.
Hi IPGeorgiev ,
looks like you have a non-csv-file in your folder.
Maybe you have to filter on filtype = "csv" just at the beginning. Then make sure that this is the reference step instead of "FakeFromFolder".
Hi IPGeorgiev
I see no reason at all why this shouldn't work with xlsx-file.
I'd suggest to start new from scratch: Import your xlsx-files from folder and check if all data comes in correctly before applying any further transformations.
Hi IPGeorgiev ,
if you start from scratch and use the combine binaries, the matching formulas for the xlsx should automatically be applied.
If I find the time, I will create a video at the weekend that describes and explains all the steps for your example.
Hi Ivan,
that's awesome! Very pleased to hear 🙂Hi ImkeF ,
many thank for the response and the sample file! Appreciate it!
I have tried to reproduce it with the following code:
However then I get this:
I am definately doing something wrong, however I cant figure it out 😞
Thanks a lot!
Best regards,
Ivan
Hi ImkeF ,
thanks a lot for the fast reply!
Yes - all of the files (the raw data) is in xlsx format. I have converted the files to .csv and the preview is correct, however I have new issues 😞 :
1. OLE DB or ODBC error: [Expression.Error] The key didn't match any rows in the table.. - I did some research on the new and found several reasons why this error might occur, however
2.OLE DB or ODBC error: [DataFormat.Error] External table is not in the expected format.. - here I have tried to fix the format of each column to meet the actual data under the columnd + checked the source files and didnt found any unsual values.
In general - is it possible to use the same approach but use the xlsx files instead and not to convert them to csv?
Many thanks!
Best regards,
IvanHi ImkeF ,
I will try it - however are you able to advise how I should modify the code so that it will work with xlsx files?
And especially this row:
GetContentFromFirstFile = Csv.Document(GetFirstFile,[Delimiter=",", Columns=20, Encoding=1252, QuoteStyle=QuoteStyle.None]),
Many thanks!!
Best regards,
IvanHi ImkeF ,
Many thanks! A video would be just awesome!
I have tried to reproduce the whole process and I got this code:
and also another version:
However I guess that something is wrong here since this doesnt give the desired result - it basically removes the newest file and keeps the rest with duplicate values 😞
I think that my error is in the first row and apparently doing this:
Date.From([Upload Date]) - Date.From([Time Date]) = #duration(7,0,0,0).
Thanks in advance!!!
Best regards,
IvanHi ImkeF ,
I have been trying the past 2 hours and I finally managed to adapt it and now it WORKS!!!!!!
BILLION THANKS for the support! I really appreciate it a lot!!
Best regards,
Ivan