Forum Discussion

lardo5150's avatar
lardo5150
Microsoft Employee
6 years ago
Solved

Import Single Excel Spreadsheet - then add changes from additional Files?

Going to start here, but not sure if it is possible to do here.

Was thinking maybe SharePoint as well, but still researching.

Might just have to do some type of Excel merge.

if anyone has any suggestions, let me know.

 

I have a tool, that exports a list of cases that an engineer closed.  

Things include case number, time, etc.

The tool goes back 180 days.

 

If I were to export this every day, the new file will be different from the one I did the previous day.

 

Is it possible, to do an export then import into PowerBi.

Then, can I setup PowerBi dataset to use that single excel file.

Then each day, import data into that file from the new files I create, but only importing data that is brand new?

Need Help

6 Replies

  • lkalawski's avatar
    lkalawski
    Resident Rockstar
    6 years ago

    Does each new file have the same structure and new data each time?
    If so, you can create a folder where you will keep all new Excel files.
    Then in Power BI in Power Query, automatically combine all files in a folder and use one merged table and build visualizations based on it. The data will be automatically added to this table.

     

    If you only want to copy new data from these new files, then you can write the appropriate code for it in Power Query.

     

    Please send more information and I will try to help.

    • lardo5150's avatar
      lardo5150
      Microsoft Employee
      6 years ago

      Yes, the newly created files will have the same column headers each time.

      OwnerEmail,CompanyName,InternalTitle,ClosedDateTime

       

      So here is what I am looking to do, in order to import new data each time, while preserving existing data and keep from creating duplicates.

       

      First import of this file called ClosedCases.xls

      Each time this file is created it is called the same thing, ClosedCases.xls

       

      Tomorrow, the file is created, and we import it (would like it to be a data refresh so it is scheduled).

      We look at the ClosedDateTime.  If any of those values match what is in the TARGET (our main file we are using), then we do not import those rows from the SOURCE (the newly created file).

      We then import everything else.

       

      Does that help?