Data Import & Export

How to Fix Power BI Datamart Server Error in Excel

Chanuka GeekiyanageChanuka Geekiyanage Oct 20, 2026 868 views

Question details

The user needs to resolve a DatamartServerError that occurs when refreshing an Excel workbook connected to a Power BI Datamart.

How to Fix Power BI Datamart Server Error in Excel
Product
Microsoft Excel, Power BI
Device & OS
not provided
Scenario
Refreshing a data query in an Excel workbook connected to a Power BI Datamart.
Observed behavior
A specific user receives a DatamartServerError upon refreshing the data, even though they previously had access and the workbook works perfectly for other users.
Before you start

Verify that your internet connection is stable and ensure you have the exact Power BI Datamart access level and workspace privileges required before modifying Excel data source settings.

Solution 1Recommended

Update Workspace Membership and Reconnect Data Source

Adjust the user's Power BI workspace access level and update the credentials within Excel's data source settings to fix authentication mismatches.

Often, granting a user access only at the Datamart level is insufficient for Excel query refreshes. The user must be a direct member of the Power BI workspace hosting the Datamart.

1
Adjust Power BI Workspace Access

Have a Power BI administrator add the affected user as a direct member of the Power BI workspace that contains the Datamart.

2
Open Data Source Settings

In Excel, navigate to the 'Data' tab, select 'Get Data', open the 'Power Query Editor', and then click on 'File' > 'Options and settings' > 'Data source settings'.

3
Edit Permissions

Select the Power BI Datamart connection from the list, click 'Edit Permissions', and re-enter the user's credentials to reconnect to the data source.

Update Workspace Membership and Reconnect Data Source
Administrator Rights Required: You will need assistance from a Power BI workspace administrator to grant the required membership access before updating the connection in Excel.
Free Microsoft Office alternative

Try WPS Office for Seamless Data Management

If you frequently encounter complex connection errors or heavy performance issues in Microsoft Excel, consider switching to WPS Office. It offers a lightweight, highly compatible spreadsheet environment that easily handles standard data formats without the heavy system overhead.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Lightweight architecture ensures fast loading and smooth spreadsheet processing.Familiar user interface allows for a zero learning curve and instant productivity.Robust, free alternative to Microsoft Office for everyday data management.
microsoft office alternative - wps office

Frequently Asked Questions

Why does the Datamart Server Error only affect one user?

This is typically caused by localized permission issues or cached outdated credentials. Even if a user has access to the specific Datamart, they may lack the necessary underlying Power BI workspace membership required to successfully refresh the query inside Excel.

Will clearing the Power Query cache delete my workbook data?

No. Clearing the Power Query cache only removes temporary data files stored by Excel to speed up background query loading. It forces Excel to fetch a completely fresh copy of the data from the Power BI Datamart, and does not alter or delete your saved spreadsheet data.

Where can I find Data Source Settings in Excel?

You can access Data Source Settings by navigating to the Data tab on the Excel ribbon, clicking 'Get Data', launching the Power Query Editor, and then selecting File > Options and settings > Data source settings.