One of Excel’s newish BI products, Power Query (PQ), is a lightning quick tool which lets you manipulate and transform your data in amazing ways. The advanced editor is not so friendly. If you’ve used Notepad++ before you’re probably in love with the highlighting it’s capable of. I use it for reading all XML files, because it makes it look great, simply navigable, and highlights keywords and syntaxes easily.
PQ is based on the M language. It’s a robust and powerful language, but can be ultimately confusing for the uninitiated. Like learning any other language, it can be a difficult start. Here is a good introduction to the language. The two best resources I’ve found on all things PQ is Ken Puls blog and Chris Webb’s blog.
Now there is a language file you can install in Notepad++ to use for editing/viewing PQ. It was written by Matt Masson, a Senior Program Manager from the SSIS team. You can view his blog post on it here:
INSTALLING THE LANGUAGE FILE
I found this a little difficult, especially as I’d never done it before. If you’re in the same boat as me, these instructions may help.
- Copy the XML code referred by a link from Matt Masson’s blog post (posted to his OneDrive here, for brevity)
Save it to an XML file located in C:\Program Files (x86)\Notepad++\
- If that doesn’t work, save it to C:\Users\[USERNAME]\AppData\Roaming\Notepad++\
- Close (if open) and open Notepad++
- Get jiggy with your power queries
If you’re having problems saving the file, I’ve uploaded it here (mlang_npp) for easy download. Please note the M language is in a state of flux, and this will most likely be outdated soon enough. While I’ll try to keep an eye on it, if you see an update or find an issue, please make a comment and let me know. Thanks!