How to Resolve Apparent Missing Data in Custom Excel Reports After a Dataset Column Update

Environment

• Platform: Microsoft SharePoint Online
• Component: Custom Excel Reports (Client-created)
• Report Storage: SharePoint Document Library
• Audience: End Users / System Administrators

Issue / Symptoms

After a new column is added to a dataset in SharePoint, users viewing a custom Excel report may experience the following symptoms:

• Rows of data appear to be missing or incomplete in the report
• The total record count displayed in the report does not match the expected number of entries
• Filtered views show fewer results than anticipated

Cause

When a new column is added to a SharePoint list or library dataset, SharePoint automatically includes that column in any existing view filters and column filters applied to the document library or list view.

Because the new column has no pre-existing values for older records, SharePoint's automatic filter treats blank or null values as a separate filter category. If the filter for the new column is not explicitly set to include all values (including blanks), records without a value in that column are excluded from the displayed results, creating the appearance of missing data.

Resolution

For End Users

1. Open the affected custom Excel report from its SharePoint document library location.
2. Identify the column header(s) that were recently added to the dataset. These columns will typically appear at the rightmost end of the dataset.
3. Click the dropdown arrow on the newly added column header to open the column filter.
4. Verify whether "(Blanks)" or empty value entries are unchecked in the filter list.
5. Select "Select All" to ensure all values, including blank entries, are included in the filter, or simply remove the automatic filter on that column if filtering is not required.
6. Click OK to apply the change.
7. Confirm that the record count now reflects the expected total number of entries.

Note: Repeat steps 3–7 for each newly added column that may be causing the filter discrepancy.

For System Administrators - Option 1: Remove Filters

1. Navigate to the SharePoint Document Library where the custom Excel report is stored.
   • Typical path: SOLABS QM Reports (Excel) > Reporting - Homegrown Reports > [Affected Report]
2. Open the offending report.
3. Navigate to File > Download to save a local copy.
4. Open the downloaded Excel file on your computer.
   • Connection error pop-ups may appear upon opening. Cancel and ignore those errors.
5. Navigate to the right of the spreadsheet until you reach the newly added column(s).
   • Select all affected columns.
   • In the ribbon, navigate to Data > Clear Filters.
   • Verify that the previously missing data is now visible.
6. Save the changes to the document.
7. Re-upload the modified report to SharePoint to make the corrected version available to end users.

Drawback: This solution requires the report to be manually modified every time a new column is added to the dataset.

For System Administrators — Option 2: Prevent New Columns from Appearing via T-SQL in Power Query

As an alternative to manually clearing filters after each dataset update, administrators can write a T-SQL (Transact-Structured Query Language) query directly within the Excel report's Power Query connection. By explicitly defining which columns are retrieved in the query (e.g., SELECT col1, col2, col3 FROM ...), any new columns added to the source dataset will be ignored entirely by the report, preventing automatic filter changes from occurring.

To implement this approach, open the Excel report and access the Power Query Editor to modify the existing data source query, replacing any open-ended column selection with an explicit list of the required columns.

Drawback: Because the query explicitly defines which columns to retrieve, any new columns added to the source dataset — along with their associated data — will never appear in the report. The query must be manually updated each time a new column needs to be included.