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

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

What Is Power Query?

Microsoft Power Query is a data transformation and cleaning engine available in Microsoft Excel, Power BI, and Azure Data Factory. Power Query works similarly in all these applications.

With Power Query, you can load data to the app from multiple sources and clean it using M code or its prebuilt transformation features. These features include removing columns, changing a data type, combining multiple datasets in Microsoft Excel, or performing a sentiment analysis on your data.

Though Power Query is an exciting tool, new users must ensure they're comfortable with Excel before starting to use Power Query. If you aren't confident with your Excel skills, visit these free training sites and courses to become an Excel master.

Exploring SQL Server Management Studio (SSMS)

SQL Server Management Studio, or SSMS as it is called, is a SQL Server platform. It is referred to as a platform because it comprises various components like Database Engine, Analysis 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 will explore its Database Engine and use a simple data table in the database.

Importing SQL Data Directly Into Excel Using Power Query

Power Query lets you import data from just about anywhere. It is one of the ten Excel Add-Ins that enables you to process, analyze, and visualize data like a pro.

To import data from SSMS, follow the steps outlined below.

  1. Open SSMS and connect with your database engine.
  2. Pick a table you want to extract from SSMS into Excel using the dropdown.
    SQL Server Management Studio (SSMS) data engine interface
  3. We'll use the 'dbo.creditcard' table for this example.
  4. Open Excel and go to Data.
  5. From Data, select Get Data > From Database > Select From SQL Server Database.
    Power Query's Get data feature
  6. Input your server and database name. You can also write an SQL statement to pull exactly what you want. This will prevent much data transformation in Power Query.
    A form asking users to input their table and database name so Power Query can connect with it

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

    An Image showing the Interface of SQL Server Management Studio (SSMS) and how to get your 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 shows a dialog box asking user to authenticate their database connection
  9. Click Connect.
  10. Power Query Navigator opens and displays all available tables in that database.
    Power Query Navigator displaying all available tables in that database.
  11. Select the SQL table you want to import.
    Power Query Navigator displaying all available tables in your database.
  12. You can select either Load or Transform Data.
  13. Load closes power query and displays the data in Excel. It would be best if you only did this when your data is clean and thus wouldn't need any transformation.
  14. Transform Data, on the other hand, ingest the data into Power Query. This allows you to perform data transformation techniques on your data.

    Always pick Transform Data. It is best practice to look at your data in Power Query before loading it into Excel. For example, you might need to change a data type before loading it. Inspecting your data before loading 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 that shows that your table has been successfully imported into Power Query
  16. Once done with your data transform, Select Load & Close.
    Microsoft spreadsheet showing your successfully imported table

Do More With Power Query

With Excel's Get Data features in Power Query, you can import data from various sources, not just SQL servers. For example, you can extract data from Microsoft Dynamic, Sharepoint, Salesforce, Azure, databases like Oracle, JSON, the web, and Social media platforms.

Also, regardless of how dirty and messy the data comes, Power Query is available for all your data cleaning and transformation needs.