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.

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

So it sounds like you need to Sort the data first and then remove duplicates. I seem to recall that a Table.Buffer step is needed in order to make the sorting "stick". ImkeF and edhans can probably be more specific.

  • ImkeF's avatar
    ImkeF
    Community Champion
    6 years ago

    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 

     

    • IPGeorgiev's avatar
      IPGeorgiev
      Helper III
      6 years ago

      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