How to import data from SQL Server to Excel using Power Query easily

In today’s data-driven world, SQL Server stands as the go-to database repository for organizations, housing their vast business data. Simultaneously, Excel remains the widely adopted versatile data analysis spreadsheet application across various departments and organizational levels.

However, getting data between these two apps can take time and effort. So, we’ll show you how to seamlessly import data from SQL Server to Excel using Power Query.

What is PowerQuery?

Microsoft Power Query is a data cleansing and transformation engine available in Microsoft Excel, Power BI, and Azure Data Factory. Power Query works in a similar way in all of these applications.

With Power Query, you can load data into your app from multiple sources and clean it up using M code or its built-in transformation functions. These functions include removing columns, changing a data type, combining multiple data sets in Microsoft Excel, or performing sentiment analysis on your data.

Although Power Query is an interesting tool, new users should make sure they are comfortable with Excel before starting to use Power Query. If you’re not sure of your Excel skills, check out these free training courses and sites to become an Excel master.

Exploring SQL Server Management Studio (SSMS)

SQL Server Management Studio, or SSMS as it’s called, is a SQL Server platform. It is called a platform because it comprises several components, such as the database engine, analytics services, integration services, and reporting services. With these services, you can have a go-to platform for all kinds of SQL-related work, from creating a table and writing queries to administrative and troubleshooting activities.

Today, we’ll explore its database engine and use a simple data table in the database.

Import SQL data directly into Excel using Power Query

Power Query allows you to import data from almost anywhere. It is one of the ten Excel add-ins that allows you to process, analyze and visualize data like a professional.

To import SSMS data, follow the steps below.

  1. Open SSMS and connect to your database engine.
  2. Choose a table you want to extract from SSMS to Excel using the drop down menu.
    SQL Server Management Studio (SSMS) Data Engine Interface
  3. We will use the ‘’ table for this example.
  4. Open Excel and go to Data.
  5. Under Data, select Get Data > From Database > Select From the SQL server database.
    Get Data from Power Query function
  6. Enter the name of your server and database. You can also write an SQL statement to get exactly what you want. This will avoid a lot of data transformation in Power Query.
    A form that asks users to enter the name of your table and database so that Power Query can connect to it

    You can get your server and database name by right clicking on the SSMS table and selecting properties.

    An image showing the SQL Server Management Studio (SSMS) interface and how to get its table properties
  7. Click OK.
  8. You can use your Windows, database, or Microsoft account to authenticate. I will use my current credentials in Windows.
    The screen displays a dialog asking the user to authenticate their connection to the database
  9. Click Connect.
  10. Power Query Navigator opens and displays all the tables available in that database.
    Power Query Navigator showing all the tables available in that database.
  11. Select the SQL table you want to import.
    Power Query Navigator showing all the tables available in your database.
  12. You can select any Burden either transform data.
  13. Burden close power query and display the data in excel. It would be better if you only did this when your data is clean and therefore doesn’t need any transformation.
  14. transform data, on the other hand, ingests the data into Power Query. This allows you to perform data transformation techniques on your data.

    always choose transform data. It’s a good practice to look at your data in Power Query before loading it into Excel. For example, you may need to change a data type before loading it. Inspecting your data before uploading it can save you time and effort.

  15. Select transform data. You have successfully imported your data into Power Query. As a result, you can perform data transformation techniques on your data.
    An interface showing that your table has been successfully imported into Power Query
  16. Once you are done with your data transformation, select load and close.
    Microsoft spreadsheet showing your table imported successfully

Do more with Power Query

With the Get Data from Excel functions in Power Query, you can import data from multiple sources, not just SQL servers. For example, you can pull data from Microsoft Dynamic, Sharepoint, Salesforce, Azure, databases like Oracle, JSON, the web, and social media platforms.

Plus, no matter how dirty and messy your data is, Power Query is there for all your data cleaning and transformation needs.