Fix Excel 2016 Data Model StorageException When Creating Relationships
Question details
Resolve a StorageException that occurs when creating multiple relationships in an Excel 2016 Data Model.

- 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.
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.
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.
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.
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.
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.
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.
Select Data > Refresh All after correcting the sources and queries. Then open Data > Relationships > New and recreate the required relationships using the corrected columns.

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.

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.




