Import Data from Database using Native Database Query (Power Query)

Important   This article applies to Power Query, a data analysis feature available for Excel that lets you discover, combine and refine data. You may need to enable Power Query in Excel. You can also download and install the most recent version of Power Query for Excel, which automatically enables it. Power Query technology is also built into the Power BI Designer, which is a stand-alone report authoring and data transformation tool for Power BI.

With Power Query, you can import data from all the databases supported by Power Query (except Microsoft Access) by directly running native database queries against the database using Power Query, instead of having to build your query using Power Query interface. This lets you use an existing query that returns the required data results, without having to rebuild the query using the Power Query interface. This feature is especially useful for importing data using complex queries that you might not want to or know how to rebuild using the Power Query interface.

Power Query enables you to specify your native database query in the SQL Statement box while connecting to a database. In this example, we will import data from a SQL Server database using the native database query. The procedure is similar for the rest of the databases supported by Power Query.

  1. Connect to a SQL Server database using Power Query. In the ribbon, on the POWER QUERY tab, select From Database > From SQL Server Database.

    Get External Data from a Database
  2. In the Microsoft SQL Database dialog box:

    1. Specify the Server and Database to connect to from where you want to import data using native database query.

    2. Expand SQL Statement to display a box where you can type or copy your native database query. After specifying the query, click OK.

      Run native database queries

  3. If you are connecting to this server for the first time, the next dialog box prompts you to select the authentication mode to connect to the database. Select an appropriate authentication mode, and continue.

    Note    If you do not have access to the data source (server/database) you are trying to connect, you will be prompted to request access to the server/database if access-request information is specified in Power BI for the data source. For more information about requesting access to data source, see Request Access to Internal Data Sources.

  4. The data is returned in the Query Editor as a result of running the native database query. Shape the data as required, and then click Apply & Close to save the changes and import the data to the sheet.

    Query Editor

See Also

Import data from external data sources

Applies To: Excel 2013, Excel 2010



Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Support resources

Change language