Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Not applicable

Analyze in Excel - 'unable to obtain list of tables from the data source'

Hi all,


I have a dataset published in the PowerBI service which is hooked up to an Azure Analysis Services cube via DirectQuery. If I try and use the Analyze In Excel option, it downloads the .odc file but then once I'm through the authentication page, it errors out with 

 'unable to obtain list of tables from the data source'


Anyone come across this before?


I have a different data set in a different workgroup that is the same set up. The only difference being that the Analysis Services cube for this was developed in VS2015 so is at 1200 compatability level.


The cube that's getting the error was built in VS2017 and is 1400 compatability level.


Anyone any clues? If there's a known issue with 1400 compatability level that isn't getting fixed anytime soon, then I guess I'll just have to re-engineer my cube, but if that's not the problem I don't want to waste time doing that.


Any advice anyone can offer would be much appreciated?




Post Prodigy
Post Prodigy

After days of suffering this last message was the fix, this is shocking!!

Skilled Sharer
Skilled Sharer

Old question, but I ran into this exact error ("Unable to obtain list of tables from the data source") when trying to Analyze in Excel from a Power BI report that connected to a model in AAS and it had nothing to do with permissions and was report specific.


To fix it, I opened the source pbix file in Power BI desktop and selected Transform Data > Data Source Settings. Both the Server and the Database name were filled in. I deleted the Database name and clicked ok. This caused the Navigator screen to pop up, showing me all the databases on the server (I only have one). However, rather than just selecting the Database name and clicking ok, I dropped one level lower and selected the model/perspective name instead. I then republished the report and had no issues analyzing in Excel.


My theory on what the issue is: the default perspective for a model is "model" - which contains everything. I had renamed that. Power BI handles the rename with no issues - if you only select the database name when you connect to the model, it defaults to the renamed perspective. But, it appears Excel defaults to the name "model" if a perspective isn't specified. If there is no perspective called model, it cannot load the list of tables - it doesn't recognize the rename. By connecting in Power BI to the specific perspective I wanted (rather than the database name), Excel knows what perspective to open and doesn't have to use a default that doesn't exist.

In Wisconsin? Join the Madison Power BI User Group.
Super User
Super User

Hi there

Could it be that the Firewall on the Azure Analysis Services is not configured to allow connections from your IP Address where you are using Excel?

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Power BI Blog

Not applicable

Hi - thanks, but I have other datasets using the same Azure Analysis Services instance that work fine.

Hi @Anonymous,


Based on research, your issue seems related to permissions, please take a look at following link about similar issue.

Reference link:

data connection wizard cannot obtain a list of databases error with standart user rights


In addition, is there any change on your datasource or has issues to use local device to connect ssas data source?



Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Community Support
Community Support

Hi @Anonymous,


I think your admin is block 'analyze in excel' feature works on AS datasource, please refer to below link to know more about this:

Analyze in Excel

Administrators for Power BI tenants can use the Power BI Admin Portal to disable the use of Analyze in Excel with on-premises datasets housed in Analysis Services (AS) databases. When that option is disabled, Analyze in Excel is disabled for AS databases, but continues to be available for use with other datasets.


Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Not applicable

Hi - thanks, but that's definitley not the problem as I can use the function on other datasets using the same datasource with no problem

Helpful resources

Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.


Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors