Using VS Code Extension MSSQL instead of Excel Power Query for Easy Database Querying

Save a few minutes every time you want to query your database to check out some data by cutting Excel out of the equation.

me@jaykilleen.com wrote this about 6 years ago and it was last updated about 6 years ago.

sql mssql get-and-transform power-query vscode
← Back to the Posts

I've been bypassing Power Query in Excel lately by going directly to our databases using SQL. This is really easy (compared to what I have been trying to do in the past) by using the Visual Studio Code Extension called mssql. Also checkout this Microsoft guide on how to use this vscode mssql extension.

Now instead of:

  • Powering up Excel
  • Opening the advanced editor in Power Query (Get and Transform)
  • Copying/pasting your snippets or writing your query
  • Loading to a worksheet
  • Total time 3 - 5 minutes

You can:

  • Open VS Code
  • Navigate to an existing text file with your .sql query
  • Hit Ctrl+Shift+E
  • Select the Database you want to Query
  • Watch the results flood into the new window
  • Total time <1 minute

I also use this Project Manager extension by alefragnani to quickly switch to my MSBI Data Catalog in VS Code. This is where I store all my .m, .pq and .sql files so I can backup in Git.

Similar

Excel IFERROR in Power Query M or Power BI

[SOLVED] Excel ISNULL, ISBLANK type custom function in Power Query M

Power Query M function to add days to a date

Power Query M Blank Function Template

[SOLVED] Resync Postgresql tables primary key

[SOLVED] First Day of Last Month in Power Query M

Blank Power Query Template for a Raw SQL Query using Microsoft SQL Server

Return Database Schema for Table Using Power Query

Add Text to an Existing Column in Power Query

MySQL Convert Datetime field in UTC Timezone to Brisbane Australia

Power Query - Left Pad an SAP Product Hierarchy with Zero

Extract Multiple Product Hierarchy Levels from an SAP Product Hierarchy Key

Using SQL to Convert Date to Financial Period Key

Blank Power Query Template for a Raw SQL Query

Removing Leading Zeros from Alphanumberic Strings using Power Query M

Remove Leading Zeros from Strings using Power Query M

Create an Empty Table with Explicit Types in Power Query M

[SOLVED] Today's Date in Power Query M

A Simple Worksheet Parameter Pattern for Power Query

Cross Join in Power Query to Combine Two Tables with Every Combination

Rails new with Webpacker, Vue and Postgres

[SOLVED] SQL Get Financial Year from Date

Add Hyperlinks to an Entire Column using VBA in Excel

Create a Simple Empty Table in Power Query with Code Example

Add a Date Model to PowerBI or Power Query

Writing a Blog Post About M Lang/M Language/Power Query Formula Language/Power BI/Get & Transform

Simple Power Query M to Display When the Report was Last Refreshed

Display a SQL Server Table Schema in PowerBI

Cheatsheet for Running SQL Against PostgreSQL from the Command Line

Creating an Atom Grammar Language Package for M Power Query Formula Language

The Microsoft Mashup Evaluation Container is a CPU and RAM hog

Power Query to Test if Date is between Start and End Dates including Nulls

Tiny Tweaks Needed for the Power Query Editor

Implementing an Environment Parameter in Power BI to Improve Performance in Development

Managing Queries in PowerBI and Power Query using Git

Add a DateKey Column in PowerBI M Lang

Loading M Queries from the Web into Power BI

Single and Multi Line Comments in Power Query Advanced Editor

Countif in Power Query or Powerbi using M Language