After I wrote the step by step guide on integrating on premise data with Power BI models I realized that I actually dispensed some bad advice. The basic premise of the post was to demonstrate how one may use Power Query to source data into a Power Pivot model in a very elegant way (at least elegant to me). The only tiny problem with the approach described in that post was the fact that it did not work. Currently, Power Pivot cannot be refreshed in Power BI if its source data is loaded using Power Query.
It is my understanding that this support should be available in future (because it makes sense, certainly at least to me it seems that it makes more sense than the lack of thereof given the fact that we are dealing with Power BI here and it just seems that all “power” things are meant to be connected somehow), but as of right now only SQL Server and Oracle data sources are supported using OLE DB drivers.
I am afraid I am still a little bit hazy on what exactly Power BI Data Management Gateway is supposed to be doing but I will try to summarize what I found out empirically and how I got the data refresh to work.
The problem we are trying to solve is the following – my SQL server database is located in my corporate data center on its own IP network while my Power Pivot Excel model is hosted somewhere in the cloud presumably in a Microsoft managed data center somewhere. The two IP networks (my corporate and Microsoft data centers) are not talking to each other. Normally, some sort of a VPN solution would have to be implemented to provide that visibility between the two environments. And it appears that that’s all there is to the Data Management Gateway.
Let’s walk through a few screen shots to see how this works. First, when we set up our data source, we need to specify either a host name or an IP address of our SQL Server
Then we use the same IP address or a host name when we set up Power Pivot connection (which means that you have to be inside of your corporate network to do it)
Note that I am using the same host name both in Data source connection set up and Power Pivot connection information. Obviously, anyone inside Microsoft data center would not be able to ping my host server – Karabas, but that’s where the Gateway comes to rescue. As long as the host name in the Power Pivot connection is the same as the one defined in the Power BI Admin Center, the data refresh will work.
One word of caution, the only way to refresh your Power Pivot workbook stored in Power BI portal is by using “Schedule Data Refresh” feature
If you try to refresh your workbook in a browser and then use Refresh All Connection options there
You will get an error telling you that this functionality is not supported – “Error: OnPremise error: We were not able to refresh the data connections. On-premise data sources can only be refreshed via scheduled refresh in Power BI for Office 365.”
business intelligist 
Have you found a way to refresh via the Excel web app? Do you know when/if this will be supported?
There is no way right now and my guess is that it won’t be supported any time soon. You will have to open the workbook locally in excel and refresh it that way. The good news is that sounds like the refresh from Power Query is going to be supported very soon
Would you happen to know if there is a way to keep DMG running when the computer is in sleep mode?
I’m getting the following error after setting my schedule refresh before I’m in the office:
“OnPremise error: We can’t communicate with the Data Management Gateway now. Ask your Office 365 admin to ensure the gateway is available and try again”
No, I would change your power management settings to turn off the monitor but not put your computer to sleep when plugged in