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.

Power Query gives you the flexibility to import data from wide variety of databases that it supports (doesn't include Microsoft Access). It can run native database queries, which can save you the time it takes to build queries using Power Query interface. This feature is especially useful for using complex queries that already exist—and that you might not want to or know how to rebuild using the Power Query interface.

Security Note: One intent of native database queries is 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 when connecting to a database. In the example below, we will import data from a SQL Server database using a native database query. The procedure is similar all other databases that Power Query supports.

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

In the Microsoft SQL Database popup window:

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

Expand the SQL Statement field and paste or enter your native database query, then click OK.

If this is the first time you're connecting to this server, you'll see a prompt 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 (both Server and Database), you'll see a prompt to request access to the server/database (if access-request information is specified in Power BI for the data source).

If the connection is established, the result data is returned in the Query Editor.

Shape the data as you prefer, then click Apply & Close to save the changes and import the data to the sheet.