About two weeks ago Version 1.0.0 of the Power Query M Language extension for Visual Studio code was released:
Here’s a link to it in the Visual Studio Marketplace. Per the overview page the initial release includes colorization, auto-closing, and surrounding support.
Here’s a simple example of an M query in VS Code (with the extension):
Once you have the extension installed you can copy an M query from the Advanced Editor window in Power BI Desktop and paste it into a new file in VS Code. You can then just save the file as a .m (per the MyPQ.m example), .M, .pq, or .PQ file. Alternatively, you may already have an M/Power Query expression saved from Notepad as an M file. If so, you can just open the file from within VS Code.
Note: You may have to update to the latest version of VS Code to install the extension.
The bottom line is we finally have some level of development support for M queries. Previously the best (only) option has been to create an M editor with Notepad++ as described in this blog.
Given the importance of M to Power BI, SSAS 2017+, and other Microsoft products I’ve been hoping for at least basic editor support like this within Power BI Desktop and/or SQL Server Data Tools. Maybe this is still in the works for SSDT for VS 2017 but I’d be very surprised and I haven’t heard anything for PBI Desktop. Clearly DAX editing/dev has been a priority for PBI and SSAS so hopefully M will get similar support in 2018.
Thanks for this notification Brett, ironic – my director saw my notepad ++ open today and recommended VS code, I downloaded and we went through it together – I was already sold but more so now (incidentally didn’t see any DAX extension but suppose it doesn’t matter with DAX Studio and recent release of v 1.7. As always enjoying and going through your content. Got your books from Packt, received the Power BI Cookbook and making time Friday into the weekend to delve. Be well!
Thanks Vinnie – Yes, DAX Studio 1.7 (all queries trace) is significant. I wish both this and the M extension would have been available for the Cookbook but it’s okay. (Years from now I/we can tell stories to younger BI developers about the days when we wrote out the M queries raw or used Notepad ++.)
>we finally have some level of development support for M queries. Previously the best (only) option has been to create an M editor with Notepad++
Not quite the case, Visual Studio 2015/17 (including the free Community edition) already had M intellisense via the Power Query SDK:
Granted, that’s a big editor if you just want to write M scripts with it (in this case N++ or VS Code are great options), but many data analysts already use VS because of SSDT.
I still hope they’ll add this to Excel and PBI, because the “Advanced Editor” in Power BI Desktop is anything but! (Horizontal scrolling because there’s not even line wrap, are you kidding me?!)