How to archive SharePoint list data while maintaining Power BI dashboard connectivity

Question

I have a SharePoint list that serves as the data source for a Power BI dashboard. The list is growing large and I need to archive older records to improve performance, but I cannot lose historical data in my Power BI reports.

What is the recommended approach to archive SharePoint list data while ensuring my Power BI dashboard continues to display both current and archived data without breaking the connection?

Specifically, I need guidance on:

Best practices for archiving SharePoint list items (separate archive list vs. status column vs. other methods)

How to configure Power BI to pull data from both active and archived sources

How to maintain data refresh schedules after implementing archiving

Any performance considerations or limitations I should be aware of

Current setup:

SharePoint Online list with over 3000 items

Power BI dashboard connected via SharePoint connector

Scheduled refresh enabled

Need to maintain historical trending and reporting capabilities

Thank you for your guidance.

Answer 1

To archive SharePoint list data while maintaining connectivity with your Power BI dashboard, you can consider the following best practices:

1. Archiving Methods:
   • Separate Archive List: Create a new SharePoint list specifically for archived items. You can move older records from the main list to this archive list. This method keeps your active list smaller and improves performance.
   • Status Column: Alternatively, you can add a status column to your existing list to indicate whether an item is active or archived. This allows you to filter out archived items in Power BI while keeping them in the same list.
   • Combination: You might also consider a combination of both methods, where you keep the most recent records in the main list and move older records to an archive list.
2. Configuring Power BI:
   • To pull data from both active and archived sources, you can create two separate queries in Power BI: one for the active list and one for the archived list. You can then use Power BI's data modeling features to combine these datasets into a single report.
   • If using a status column, you can filter your query to include both active and archived items based on the status.
3. Maintaining Data Refresh Schedules:
   • Ensure that both the active and archived lists are included in your scheduled refresh settings in Power BI. This way, any changes in either list will be reflected in your dashboard.
   • If you have separate lists, you may need to set up refresh schedules for both lists individually.
4. Performance Considerations:
   • Be mindful of the SharePoint list threshold limit (typically 5,000 items). If your list exceeds this limit, it may affect performance. Archiving older records can help manage this limit effectively.
   • Monitor the performance of your Power BI reports after implementing archiving. If you notice any lag, consider optimizing your queries or data model.

By following these practices, you can effectively archive your SharePoint list data while ensuring that your Power BI dashboard continues to function smoothly and retains historical data for reporting purposes.

---

References:

• Archive SharePoint Online document library content
• Create a report on a SharePoint List in Power BI Desktop