You can also 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 enables you to 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.
- Connect to a SQL Server database using Power Query. In Excel, on the POWER QUERY tab, click From Database > From SQL Server Database.
- In the Microsoft SQL Database dialog box:
- Specify the Server and Database to connect to from where you want to import data using native database query.
- Expand SQL Statement to display a box where you can type or copy your native database query. After specifying the query, click OK.
- 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.
- 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.
Import data from external data sources