Skip to main content

Microsoft Power BI Blog

Scheduled Data Refresh for Power Query

Headshot of article author The Power BI Team

Microsoft
2 May, 2014

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.

Schedule refresh of workbooks with Power Query Connections (SQL and Oracle only)

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.

2. In the connection info page, enter a valid Power Query connection string, and click next. To get the connection string from an Excel workbook see Get a connection string from a data table for instructions to copy a Power Query connection string. Getting the Power Query connection string from Power Pivot is not supported yet.
3. All the data sources included in the Power Query connection will be shown in the data source info page. Excel workbook with data model using Power Query connection cannot be refreshed until all the data sources in the Power Query connection are configured. 

The status of the data source can be Not configured because of the following reasons:

    • The data source hasn’t been configured. You need to create the data source with proper connection information.
    • The data source has been configured in your tenant, however, some data source settings are missing. You need to update the data source with the missing piece.

Here’s a step-by-step guidance to create the data source in your tenant:

    1. Select a data source that has not been configured.
    2. Provide a user friendly name for the data source, which is used to identify the data source in Admin Center.
    3. (optional) Provide a description for the data source.
    4. Select a gateway to host the data source. Only the new gateway you created will show up in the drop down list. In case, you do not see any gateway listed, make sure you have upgraded your gateway to the latest version (which supports Power Query refresh) and also ensure all the data sources in the Power Query connection are hosted on the same gateway.
    5. Select a Privacy Level, which is a Power Query connection specific setting and defines the degree that one data source will be isolated from other data sources. For more information, see Power Query Privacy levels.
    6. Once all the information above are provided and validated, the credentials button will be enabled. Click credentials to launch the data source settings dialog box. Select Credentials type (Windows or database), enter your credentials for the data source, check Encrypt connection checkbox if you would like to establish an encrypted connection to the database. You must test connection before clicking ok to save your credentials.

 

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.

Limitations for Power Query refresh in this update

  1. Getting Power Query connection string from Power Pivot is not supported yet.
  2. Data source support is limited to SQL Server and Oracle. Native queries (custom SQL statements) are not supported. Other data sources will be supported in later updates.
  3. Gateway support:
    1. Gateway version 1.1 and above with .NET Framework 3.5. The Gateway must be upgraded to the latest version to support Power Query refreshes.
    2. All data sources in the Power Query connection must be hosted on the same Gateway. This is for security consideration.
  4. Data source details:

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.

.NET Framework 4.5.1 requirement

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.