We have an issue tracking log sent via an automatic email every hour of the day as a .xlsx file. Each file is dated in the file name and tables have the following format.
| Unique ID | PL Identifier | Date Created | Date Reviewed | Date Closed | Company | Status | Location |
| A unique value to each issue | An identifier number that may be duplicate due to multiple devices possibly offline when created | | | | Assigned company to resolve issue | Issued,Work Required,Ready for Review, Ready to Close, In Dispute,Closed | Floor>Room No. |
I would like to create a table showing the changes on a daily basis for each Company on a daily basis showing both the quantity of total items along with the difference between the current and previous day.
My current process was to have all the files kept on a sharepoint folder and I was able to query the 1PM report for each day and then combined all the reports into a single table. I then used a pivot table to create the matrix but I cant create a difference off of a pivot table. Also loading times are extremely long (10+ minutes), assuming due to the method I am using and the quantity of rows per excel file (25k+ rows).
What would be a better approach to create a delta table and reduce the loading time?
