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

[lardo5150]

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?

[lkalawski]

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]

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?

[lkalawski · Accepted solution]

It was described very well here:

Load history  

[lardo5150]

How do I remove duplicates though?

So when I import the file and append to the new query, if there are duplicates, it appends them.

I need to remove these on each append operation.

[lardo5150]

I should add, duplicates will be based on several columns.

They could close the same case twice, that has happened, but if the rows are identical, then that would be a duplicate.

When I say row, I mean, case number, close date, company name, etc... are all the same.

[lardo5150]

Sorry, still having one issue with this.

I had missed a step in that article, which was to change the "result" spreadsheet to point to itself.

So I followed his steps in that article.

Created a Duplicate of CasesClosed.  I renamed it to ClosedResults

I set it up to append CasesClosed.

The source though for ClosedResults, is pointing to the same source as CasesClosed.  So I found it was just an exact copy of the data, it was not appending it.

In his steps, he has you changing the source to:

= Excel.CurrentWorkbook(){[Name="Result"]}[Content]

"result" being what he used, but I am assuming this would be ClosedResults for me.

the problem is no matter how I change it, I keep getting errors.

The source for CasesClosed and Closedresults looks like this right now:

= Excel.Workbook(Web.Contents("https://microsoft-my.sharepoint.com/something/something/Documents/ClosedCases.xlsx"), null, true)

I tried a few ways, but kept getting an error bout 0 arguments were passed to a function which expects 1 and 2

How do I point this spreadsheet, at itself, as the source per his article?