Power BI Hangs on Loading Data to Model

[RAC_GAVIN]

I'm looking for someone that can give guidance here .. or at least direct me into more detailed troubleshooting.  So here is where I am at:

Was looking to install the On Premise Gateway and then have a dataset that refreshes on a schedule from an on premise sql server.  The Gateway installed without issue and the status shows online.  

However!  When I try to create the dataset through Power BI desktop it simply hangs on the loading data to model... step.  This is happening on multiple machines on the same network.  I can preview the data successfully and the data loads in Power Query too .. but when I hit apply or close & apply from power query it hangs at the same step (see screenshot below .. I gave it over an hour on the first time since I had never seen this happen before).  If I use direct query it does allow me to start designing the report (it does not hang) .. but direct query is not appropriate for my requirements here.  

Things I have tried on this network:
1) Multiple SQL Servers including installing a new database engine on the machine with Power BI Desktop installed.
2) Tried multiple machines with Power BI Desktop (two) - connecting to 3 SQL servers from each machine.
3)  Both SQL Server (Basic) and Windows Authentication - both authentications can see the tables / preview the data / see data in power query.

I am stumped.  Any help / input would be greatly appreciated.

[RAC_GAVIN · Accepted solution]

It seems that a reboot of the machine running PBI desktop fixed the issue.  I got prompted for a Windows Update and after install of that update the issue went away.  Had nothing to do with firewall or network traffic (that didn't seem logical to me since I could preview the data and communicate with the SQL Server using Direct Query and also I could work with the data in Power Query).  Anyway I hope this helps someone else.

[v-yinliw-msft]

Hi @RAC_GAVIN ,

Because of you mentioned that you use the DQ mode to connect the database correctly, but the mode which you need IMPORT OR LC is unsuccessful. It may be the dataset problem.

The import mode needs that the dataset is not allowed over 1GB. Or your model has too many functions.

Could you please check your dataset?

Best Regards,

Community Support Team _Yinliw

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

[RAC_GAVIN]

Hi - No I can say the dataset or anything in the report is definitively Not the issue.  I downloaded a MSFT sample database backup and I encounter the same problem on this network, but when I use that same backup on a laptop with SQL Server (not connected to any network) the data from the table is loaded into the model without issue.  

I am guessing it has to do with something network related, although I am not sure what that could be yet.  I'm not sure if the Firewall is a logical place to think the problem originates from .. as this is internetwork traffic - it's simply power bi desktop communicating with the SQL Server?  If anyone has a good suggestion on how to be most efficient in troubleshooting please advise.  

I have pinged an IT resource for help and think this article may be of help to garner some kind of useful information on where it is hanging (since I am not getting an error message and it just spins forever): Power BI Desktop diagnostics collection - Power BI | Microsoft Learn.

My next planned step is to turn on diagnostics and see where the log file stops and then compare that to the laptop with the same database that doesn't have the problem.

[Shaurya]

Hi @RAC_GAVIN,

I cannot say for sure why this is happening in your case but you might want to take a look at the hosts that are allowed to access your DB. I think you should check if Power BI URLs are whitelisted or not.

Links that might be helpful:

https://learn.microsoft.com/en-us/power-bi/admin/power-bi-allow-list-urls

https://www.microsoft.com/en-us/download/details.aspx?id=56519

Mark this post as a solution if that works for you!

[dcrosseto]

Hi @RAC_GAVIN ,

If I would have to guess something, I would check If the firewall is blocking this connection with PBI Services.

If this is it, Microsoft has a list of IP's you can apply to your firewall as trusted endpoints.

Regards,

Douglas.