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

Note: Power Query is known as Get & Transform in Excel 2016. Information provided here applies to both. To learn more, see Get & Transform in Excel 2016.

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.

Security Note: Native Database Queries are intended to be non-side effecting. However, Power Query does not guarantee that the query will not affect the database. If you run a Native Database Query written by another user, you will be prompted to ensure that you're aware of the queries that will be evaluated with your credentials.

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

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×