Archive Strategy for SharePoint List Approaching 5000-Item Threshold While Maintaining Power BI Data Integrity

Question

Current Situation:

SharePoint list with 4000+ items approaching the 5000 item list view threshold

List is the primary data source for a Power BI report requiring complete historical data for accurate reporting

List continues to grow with regular submissions via Power Apps form

Need to prevent threshold errors while maintaining 100% data access for reporting

What I've Already Done:

Created indexed columns on primary date fields

Created filtered views (Last 90 Days) to avoid threshold issues for end users

Threshold warning persists in List Settings

What I Need Help With:

Archiving Strategy: Best practice for archiving older records (e.g., 2+ years old) to a separate "Archive" list without losing data

Power BI Integration: How to configure Power BI to pull from BOTH the active list AND archive list, then combine them into a single dataset for seamless reporting

Automation: Whether Power Automate can automatically move items to archive based on age criteria

Alternative Solutions: If there's a better approach than archiving that maintains data integrity and avoids threshold issues long-term

Answer 1

To address the challenges you're facing with your SharePoint list approaching the 5000-item threshold while ensuring data integrity for your Power BI reports, consider the following strategies:

Archiving Strategy

1. Create an Archive List: Set up a separate SharePoint list designated for archived records. You can manually or automatically move items older than 2 years to this list.
2. Use Power Automate: Implement a Power Automate flow that triggers on a schedule (e.g., monthly) to check the age of items in your primary list. If an item is older than your specified threshold (e.g., 2 years), the flow can move it to the Archive list. This helps maintain the active list below the threshold while preserving historical data.

Power BI Integration

1. Data Source Configuration: In Power BI, you can connect to both the active SharePoint list and the Archive list. Use the Power Query Editor to load data from both sources.
2. Combine Datasets: After loading both datasets, you can append the Archive list to the active list within Power BI. This allows you to create a unified dataset that includes all historical data for reporting.
3. Data Model: Ensure that your data model in Power BI reflects the necessary relationships and that you account for any differences in schema between the active and archive lists.

Alternative Solutions

• Filtered Views: Continue using filtered views to limit the number of items displayed to users, but also consider creating views that only show items from the Archive list when necessary.
• Retention Policies: Explore setting up retention policies for your SharePoint list to automatically manage the lifecycle of items based on your organizational needs.

By implementing these strategies, you can effectively manage your SharePoint list's growth, maintain data integrity for Power BI reporting, and avoid threshold-related errors.

---

References:

• Best practices for storage management
• Power BI implementation planning: Retire and archive content