Download the Data Management Gateway
Extended data source support
Step-by-step Power Query connection configuration
We are excited to announce new data sources for the scheduled data refresh feature of Power BI. To support these enhancements, a new version of Data Management Gateway is available. Installation package (version 1.2) can be downloaded from Microsoft Download Center.
Supported data source types from Power Query connection string in the Data Management Gateway version 1.2:
Newly supported data source
File (CSV, XML, Text, Excel, Access)
SharePoint List (Online)
SharePoint List (On-prem)
Anonymous, Windows, Basic, Key (Data Market)
Key (Data Market)
Key (Azure Account)
Azure Blob Storage
Azure Table Storage
Query that is not accessing data sources
: Native queries (custom SQL statements) for relational databases are not supported yet.
:.It is best to place file and folder data sources on shared folders, so that the accessibility of the data sources is more reliable. However, if the data sources are available on every machine with the gateway instance installed, the Power Query connection is still able to get refreshed successfully.
: Web API key and OAuth2 are not supported yet.
Here is a step by step guide showing the UI improvements for Power Query connection configuration introduced since our May update
1. In the data source page, click new data source > Power Query.
In addition to SQL Server, Oracle and a Power Query connection, the July update to Power BI Admin Center also supports SharePoint Online Document Library for data indexing. Please refer to related online documentation for more information.
2. In the connection info page, enter a valid Power Query connection string, and click next.
We’ve added more information on this page to help you determine the information needed for a valid Power Query connection string, as well as a complete matrix of supported data sources. Please note that currently we only support Power Query connection strings from the DATA tab in the Excel workbook, please refer to online document for details. Direct copying Power Query connection string from Power Pivot is not supported. A valid Power Query connection string should contain:
3. All data sources in the Power Query connection will be shown in the data source info page.
To improve the user experience, we’ve added a DETAIL column to demonstrate the information of the data source, and the Details in the right pane is expanded by default.
4. In order to configure a non-configured data source, specify Name and Description (optional) for the data source and select a Gateway. Then click the set credentials button to launch the data source settings dialog. The layout of the data source settings dialog depends on the Credential type for the data source. For example, you can specify Database or Windows authentication for Teradata, and Account key for Azure HDInsight.
You can specify other data source settings in the dialog including privacy level and encrypt connection for relational databases. You must test connection before clicking ok to save your credentials. And don’t forget to click save in the Admin Center to commit your modifications to the data source . The data source status will be changed to configured.
Please note you will need the latest version (v1.2) of Data Management Gateway for data sources other than SQL Server and Oracle. And all data source in the Power Query connection still need to be on the same gateway. However, you can add more instances to one gateway to solve any scalability issues, which is also a new feature in this July update.
5. When all data sources within the Power Query connection are appropriately configured, you should test Power Query connection before clicking next to make sure the Power Query connection works.
6. 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.
Now you can successfully refresh your Excel workbook with a data model using the Power Query connection on the Power BI site!
This is great, just tested it with a folder with csv files... it WORKS....
Keep up the great work, you make my working life more fun with every update.
Thanks Wouter! We love that you love it.
Does this imply that SSAS Tabular is now supported as a Data Source with DMG?
This is great progress. Will sass multi dimensional and tabular be supported soon?
One question still remains: When will SSAS Multidimensional and Tabular data sources be supported?
This is great, but how come Teradata is supported, and not MS it's own SSAS Tabular? What are the priorities?
In my opinion it is a bit of a failure to promote PowerBI and Tabular, but not being able to host a power BI site with a connection to a Tabular model... We're still doing workarounds with local excel distribution (argh).
Could you please tell me when this is supported -
Thanks for the great info!
Any idea when scheduled data refresh for Power Query for Project Online will be supported?
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