Update: The Data Refresh experience on Power BI has changed. For more details read this article and try the new Power BI today.
We are excited to bring you scheduled refresh for workbooks containing Power Query connections to SQL and Oracle databases. This is the first step towards adding broad support for Power Query across all data sources. To support this capability, we have released a new version of the Data Management Gateway. The new installation package (version 1.1) can be downloaded from Microsoft Download Center.
You can now configure on-premises data sources for Power Query connections in the Power BI Admin Center, so that workbooks with data models using Power Query connections can be refreshed via scheduled refresh.
Currently, we only support Power Query connections containing SQL Server and/or Oracle databases. You will need the .NET Framework Data Provider for SQL Server to connect to SQL Server databases, and Oracle Data Provider for .NET to connect to Oracle databases (Oracle 9i or above).
Here’s a step-by-step guide to configure Power Query connections in the Power BI Admin Center:
1. First you need to create a data source. In the data source page, click new data source > Power Query.
The status of the data source can be Not configured because of the following reasons:
Here’s a step-by-step guidance to create the data source in your tenant:
You can expand View additional details to view the detailed information of the data source, e.g. the Connection provider and the Connection string.
g. Click save, and the data source status is changed to configured.
When all the data sources within the Power Query connection are appropriately configured, the test Power Query connection button is enabled. It is suggested to test Power Query connection before clicking next, but if the operation takes too long, you may skip the testing by clicking next.
In the users and groups page, specify users and groups that are allowed to access these data sources to refresh Power Query connections. If a data source already exists, the specified users and groups will be appended to the existing users and groups list of the data source.
Once the Power Query connection string is successfully configured, you can schedule data refresh for Excel Workbooks containing the Power Query connection on the Power BI site.
Currently the details of the data source can only be viewed by expanding View additional details. Improved user experience will be available in later updates.
With .NET Framework 4.5.1, you will no longer see a refresh failure containing an error message in the Windows event log as “This operation cannot be performed on a completed asynchronous result object”. For this May update, .NET Framework 4.5.1 became a prerequisite to set up a gateway.
For more information, see Troubleshooting Power BI for the IT Pro.
Also, for more information check out the website to learn more about Power BI for Office 365 and start a free trial today.
It seems a link was forgotten in step 3. "See Get a connection string from an Excel workbook for instructions to copy a Power Query connection string from Excel."
Thanks Koen. The issue has been fixed. We have added a hyperlink to the text.
Says this is the first step, any ETA on when will more data sources be supported?
I'm currently playing around with the new tools for Power Query in combination with CRM online, and I can see that the new power Query functions works perfectly EXCEL, and I can add data to the datamodel in EXCEL directly from CRM Online. however I dont see a way to use the Schedule update function for CRM ONLINE in combination with the scheduled update functions in power BI. Is I the only one who have the same problem or request, or am I missing something.
"Native queries (custom SQL statements) are not supported"
Any idea when custom SQL statements will be supported? I have a customer that I have to take off Power BI in this moment, as they pay money for nothing. Without this feature Power BI cannot be used for any serious data analysis, except some simple show offs. The workaround to bring tables to Excel first and then run queries on them will not work as in their case:
1. The datasets are too large
2. There are too many spreadsheets that would have to be modified
While configuring Power Query data source connection, get error in data source info as "The Power Query connection string contains data source types that require the latest Data Management Gateway. Please update an existing gateway or create a new gateway with the latest version and try again. Review the data source support matrix ". Please suggest. Sharing additional information of the environment- The DMG version is 1.1.5226.8. The data source in Power Query is SQL Server on premise database which is also configured successfully in DMG data sources.
Is there a plan to bring this functionality into On Premise SharePoint?
Comments in this blog are open and monitored for each post for a period of one week after the posting date. If you have a specific question about a blog post that is older than one week, please submit your question via our Twitter handle @MSPowerBI