Data Import & Export

Fix Excel 2016 Data Model StorageException When Creating Relationships

John WilsonJohn Wilson Nov 22, 2026 868 views

Question details

Resolve a StorageException that occurs when creating multiple relationships in an Excel 2016 Data Model.

Fix Excel 2016 Data Model StorageException When Creating Relationships
Product
Microsoft Excel 2016 Data Model
Device & OS
not provided
Scenario
The user is relating a date table and other source tables, including a Sales table transformed with Power Query.
Observed behavior
Excel displays a Microsoft.AnalysisServices.BackEnd.StorageException while relationships are being created, even though the source tables appear to contain valid data.
Before you start

Identify the columns used in each relationship and note which table should contain the unique key. Check the earliest and latest dates in the related tables and confirm that the source queries can be refreshed.

Solution 1Recommended

Check the date table and relationship keys, then refresh the model

Use this approach to correct missing dates, duplicate keys, incompatible data types, and conflicting relationship paths before recreating relationships.

1
Check the date range

Open the date table source or its query and compare its earliest and latest dates with the dates in each related table. Extend the date table if necessary; for example, include 2017 if Sales contains 2017 dates.

2
Verify unique keys

Inspect the date column in the date table for duplicates and blanks. In Power Query, you can select the column and use Home > Remove Rows > Remove Duplicates, but first check why duplicates exist so that you do not remove meaningful records.

3
Check types and query results

Open Data > Queries & Connections and edit the relevant queries. Check that both relationship columns use compatible data types, and inspect the unpivoted Sales result for errors, unexpected duplicates, or date-time values with time components.

4
Review existing relationships

Open Data > Relationships and inspect the relationships and key columns. Remove or revise relationships that use the wrong key or create conflicting paths; the lookup side of each one-to-many relationship must have unique values.

5
Refresh and recreate relationships

Select Data > Refresh All after correcting the sources and queries. Then open Data > Relationships > New and recreate the required relationships using the corrected columns.

Check the date table and relationship keys, then refresh the model
If the error remains: These checks address common data and relationship issues but do not guarantee a fix for every StorageException. Test relationships one at a time to identify which tables or columns trigger the error.
Free Microsoft Office alternative

Work with Excel files in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for Excel file formats, making it easier to move everyday workbook tasks between suites. It does not repair Excel Data Model StorageException errors; check complex model features before migrating.

Open and edit common Microsoft Excel formats, including XLSX and XLS.Review and edit source-table data in a familiar spreadsheet interface.Use a free, lightweight office suite for everyday workbook tasks.Move standard spreadsheet files between Microsoft Office and WPS Office.
microsoft office alternative - wps office

Frequently Asked Questions

Can a date table that starts in 2018 cause problems with 2017 sales?

Yes. Sales dates from 2017 will not match a date table that begins in 2018, which can affect analysis. Extend the date table to cover the dates in all related tables, then select Data > Refresh All.

Does an unpivoted Power Query table prevent additional relationships?

No. Unpivoting does not by itself prevent relationships. Open the query from Data > Queries & Connections and check its output for errors, changed data types, and duplicate values on any column intended to be a unique lookup key.

Which side of a relationship needs unique values?

The lookup or one side of a one-to-many relationship must have a unique value for each key. For a date relationship, check the date table's date column for duplicates and blanks before creating the relationship under Data > Relationships.

Why might matching dates fail to relate correctly?

The columns may have incompatible data types, or one may contain date-time values with time components. Inspect both columns in Power Query, set compatible types as appropriate, and refresh the model before recreating the relationship.