Import data from database using native database query power query

You have the flexibility to import data from wide variety of supported databases. You can also run a native database query, which can save you the time it takes to create the same results in Power Query.   

Warning: If you run a Native Database Query written by another user, you are prompted to ensure that you're aware of the queries that will be evaluated with your credentials.

You can specify a native database query in the SQL Statement box when connecting to a database. In the example below, we import data from a SQL Server database by using a native database query. The procedure is similar all other supported databases.

1. Select Data > Get Data > From Databases > From SQL Server Database. The SQL Database dialog box appears.

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

3. Select Advanced Options.

4. Enter your native database query in the SQL statement box.
   
   
   
   Additional options may be available depending on the database.

5. Select OK.

6. 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. 

7. The data is returned to the Power Query Editor.

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

Note    When you import an Access database, it does not display an SQL text box, but in the Navigator window, queries are listed. An Access query is based on SQL and you can select a query to effectively specify an SQL statement.

To edit the contents of the SQL statement box:

1. Select a cell in a query based on the database source.

2. Select Query > Edit. The Power Query Editor appears.

3. Under Applied Steps in the Query Settings pane on the left, right click the Source step, and then select Edit Settings. The Navigator dialog box appears.

4. Make your changes in the SQL Statement box, and then select OK.
   
   The new results appear in Data Preview.

5. When finished editing the query, select Home > Close and load.
   
   The new results appear in the worksheet.

See Also

Power Query for Excel Help

Import data from external data sources

Refresh an external data connection in Excel

Import data using native database query (docs.com)