Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
radick201
Frequent Visitor

Issues Tracking Data Model

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 IDPL IdentifierDate CreatedDate ReviewedDate ClosedCompanyStatusLocation
A unique value to each issueAn identifier number that may be duplicate due to multiple devices possibly offline when created   Assigned company to resolve issueIssued,Work Required,Ready for Review, Ready to Close, In Dispute,ClosedFloor>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?

3 REPLIES 3
lbendlin
Super User
Super User

Load the Excel data into a database like SQL Server. Include the file name so you know where each row came from.

Unfortunately we dont have access to upload to a SQL server.  Any other possible solutions?

Place the Excel files on a OneDrive. Then access them via the Sharepoint Folder connector.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors