topic unable to load data from SQL Azure in Power Query

unable to load data from SQL Azure

ericreiner — Wed, 22 Jul 2015 17:37:36 GMT

I have been trying to use PowerBI with an Azure database. PowerBI can see my table structure but always gives an error trying to retrive data, making the tool useless. The error I keep getting is:

Couldn't load the data for this visual

Couldn’t retrieve the data for this visual. Please try again later.

Activity Idc6714063-d785-4cd1-927a-ea65c677f492

TimeWed Jul 22 2015 12:35:01 GMT-0500 (CDT)

Version11.0.9167.446

Cluster URIhttps://wabi-us-north-central-redirect.analysis.windows.net

I did try upgrading the level of the Database to see if that was the error but right now it is at a S0 and still does not work. Any clues on how to get PowerBI to read the data?

Re: unable to load data from SQL Azure

mhenning — Wed, 22 Jul 2015 17:57:38 GMT

I am getting the same issue. I confirmed that the firewall settings on the Azure SQL server were set to "on" for "Allow access to Azure services"

I have confirmed there is data in my database and table, power BI can see the table and columns, but will not load any of the data for some reason.

Re: unable to load data from SQL Azure

jeffrey_wang — Wed, 22 Jul 2015 19:06:20 GMT

There is a bug that's fixed recently. It will take a couple of weeks for the fix to propagate through several stages of testing. If your case is urgent, please file a support incident. The bug manifests itself when an Azure SQL table contains columns of certain datetime data types, an incorrect query is generated to map the table into Power BI.

Re: unable to load data from SQL Azure

mhenning — Fri, 24 Jul 2015 14:13:30 GMT

Can I modify the DATETIME data type to get this working?

I submitted a ticket to get this working, how long will it take?

Re: unable to load data from SQL Azure

madion — Tue, 28 Jul 2015 03:57:25 GMT

I have fixed this issue by changing my date columns from date to datetime and then re-creating the dataset (I still haven't figured out how to refresh a SQL Azure schema?!)

ALTER TABLE [tablename]
ALTER COLUMN [columnwithdateformat] datetime

Thanks for the previous posts as the error message doesn't give any indication on what's wrong.

Re: unable to load data from SQL Azure

mhenning — Wed, 29 Jul 2015 15:13:11 GMT

FWIW .. I prefer to use a view, since I push data into a source table using a scheduled job. I found I can make small changes to the view, and it reflects in the reports. I cannot change the schema, or data type though. That is very frustrating. I was able to alter a view and use select case to change some of the data going into powerbi. As long as I stick with the same datatype, the report handles it fine but changing from an integer to a varchar and power bi cannot handle it. I have to delete then re-add the dataset to grab the changes.

Re: unable to load data from SQL Azure

TPalmer — Wed, 29 Jul 2015 15:37:45 GMT

Thanks for the feedback, we are looking to improve the connector so that things like schema changes don't require you to recreate the connection. It would be great if you could add your votes to the suggestion as well:

https://support.powerbi.com/forums/265200-power-bi/suggestions/8521726-allow-schema-refresh-for-sql-azure-direct-connect

Re: unable to load data from SQL Azure

mhenning — Wed, 29 Jul 2015 16:40:17 GMT

Please add this. the schema changes thing is KILLING me.

Re: unable to load data from SQL Azure

Sharon_Ruiz — Fri, 22 Apr 2016 13:40:17 GMT

Having the same issue...is there any resolution?

Re: unable to load data from SQL Azure

Anonymous — Mon, 14 Sep 2020 12:28:40 GMT

I had a similar error, and the cause was a semicolon at the end of one query added by a formatter.