Table is Empty when Connecting to a View in Azure SQL Database from Power BI

[m196804]

Hi

I have a Power BI report deployed to the service which has been working fine.

The data sources for it are a number of views in an Azure SQL Server database which are refreshed every night. 

Following last night's refresh, I noticed there was no data in the report. I tried refreshing again and same thing. 

I then opened the PBIX file in Power Query in Desktop, refreshed the queries and found they all have the same issue. 

If I connect directly to a table, I can see the data in it but when I'm connecting to views I get "no data" in Power Query.

The queries are in import mode. 

I have tried deleting and recreating the local and global data sources and I can see the data fine in SSMS using the same credentials I am using to connect from within Power BI.

Any suggestions?

Thanks 

[Anonymous · Accepted solution]

Hi @m196804 ,

First, please ensure that the credentials used in Power BI to access the Azure SQL Server have the necessary permissions to view the data in the SQL Server views. Sometimes, permissions might be set correctly for tables but not for views.
And please review the configuration and optimization of your Azure SQL Server views. Ensure they are efficiently designed for Power BI consumption. This includes checking for any complex calculations or aggregations within the views that might be optimized or pre-aggregated.
If your Power BI model uses complex Power Query queries to transform data from Azure SQL Server views, consider simplifying them. Power BI works best with queries that map directly to a single relational database source table or view without requiring transformations. Complex queries might not map correctly to the underlying views, leading to data not showing up.

If the above steps do not resolve your issue, could you please provide the following information to further assist you?

Have there been any recent changes to the views or the database schema?
Are you able to preview data from these views in Power Query Editor before the refresh, or does it show "no data" immediately?
What version of Power BI Desktop are you using?

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[m196804]

Hi @Anonymous 

thank you for your reply.

It turned out not to be Power BI causing the issue but a join in one of the views.