Update: The Data Refresh experience on Power BI has changed. For more details read this article and try the new Power BI today.
In this Post
Download the Data Management Gateway
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.
Extended data source support
Supported data source types from Power Query connection string in the Data Management Gateway version 1.2:
| Gateway version | Newly supported data source | Authentication type |
| 1.2
| SQL Server | Windows, Basic |
| Oracle | Windows, Basic | |
| File (CSV, XML, Text, Excel, Access) | Windows | |
| Folder | Windows | |
| SharePoint List (Online) | Anonymous | |
| SharePoint List (On-prem) | Anonymous, Windows | |
| OData Feed | Anonymous, Windows, Basic, Key (Data Market) | |
| Azure Marketplace | Key (Data Market) | |
| Azure HDInsight | Key (Azure Account) | |
| Azure Blob Storage | Key (Azure Account) | |
| Azure Table Storage | Key (Azure Account) | |
| PostgreSQL | Basic | |
| Teradata | Windows, Basic | |
| Sybase | Windows, Basic | |
| DB2 | Windows, Basic | |
| MySQL | Windows, Basic | |
| SQL Azure | Basic | |
| Query that is not accessing data sources |
[1]: Native queries (custom SQL statements) for relational databases are not supported yet.
[2]:.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.
[4]: Web API key and OAuth2 are not supported yet.
Step-by-step Power Query connection configuration
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:
-
- Provider=Microsoft.Mashup.OleDb.1;
- Data Source=$EmbeddedMashup(SomeGUID)$;
- Location=SomePowerQueryName;
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!
Additional information
- Please refer to data source prerequisites for required providers to access the corresponding data sources. Please note that #literals, Web, SAP BusinessObjects, Active Directory, HDFS, Facebook, Exchange and Current Excel workbook are not yet supported sources.
- Power Query connection string from Power Pivot is still not supported. You can only get a valid Power Query connection string from a data table.
- All data sources in the Power Query connection must still be hosted on the same gateway. You may consider adding more instances to the gateway to solve any scalability problems.
- Data source details are now displayed in the DETAIL column to improve visibility when configuring a Power Query connection.
- Since privacy levelis a part of data source settings for Power Query connections, it is required to specify a value for privacy level and to confirm the credential before trying to update privacy level.
- Privacy level for existing SQL Server and Oracle data sources will by default be assigned as organizational. You can update the privacy level afterwards.
- Privacy level for anonymous data sources is fixed to be public.
- For data sources not used in Power Query connections, the value of this setting will not take effect and it is safe to choose an arbitrary value.