Forum Discussion

IPGeorgiev's avatar
IPGeorgiev
Helper III
6 years ago
Solved

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:   File...
Need Help
Tutorial Requests
  • ImkeF's avatar
    ImkeF
    6 years ago

    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.

     

ImkeF's avatar
ImkeF
Community Champion
6 years ago

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.

 

 

IPGeorgiev's avatar
IPGeorgiev
Helper III
6 years ago

Hi 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,
Ivan

  • ImkeF's avatar
    ImkeF
    Community Champion
    6 years ago

    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.

     

  • ImkeF's avatar
    ImkeF
    Community Champion
    6 years ago

    Hi Ivan,
    that's awesome! Very pleased to hear 🙂

  • IPGeorgiev's avatar
    IPGeorgiev
    Helper III
    6 years ago

    Hi 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,
    Ivan

  • IPGeorgiev's avatar
    IPGeorgiev
    Helper III
    6 years ago

    Hi 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