Get your own Power Query Editor using Notepad++

by LarsSchreiber Member on ‎03-21-2017 09:52 AM

Hello Power Query enthusiasts,

 

many of you know that Power Query is an amazing tool for data import and data transformation. It is powerful an easy to use. But there is always a BUT. It is a pain to write custom M code. The Power Query Advanced Editor comes without intellisense (auto completion), no parameter hints, no syntax highlighting, no help texts, which explain what the functions do, no nothing. The fact, that M is case sensitive doesn’t make it easier at all.

 

In 2015 I read the following article, written by Matt Masson. Matt is a Senior Program Manager at Microsoft and member of the Power BI Developer Team. He showed how to create an editor for Power Query with Notepad++. At that time I did not even know that it was possible to create a custom language in Notepad++. Thanks a lot Matt 

 

Matt’s tool already had intellisense and syntax highlighting. What I was still missing was parameter hints and help texts. The number of Power Query functions are getting more every month and I am always looking for the right function for my specific problem. This is why I was investigating a bit and found a solution, which I hope will be helping others as well.

 

I use my editor mainly in two scenarios:

 

Scenario 1: Finding the right function for my problem

 

GetYourEditorForPowerQuery_twitter.gif

 

Using the editor makes it much easier to find the function that fits your needs. Especially the help texts help you to get more familiar with the M language.

 

Scenario 2: Taking a look at M code with highlighted keywords and comments

 

01.jpg

 

Syntax highlighting helps to keep (or get) an overview over your M code. Commenting becomes more important, the longer and complex your M code gets.

If you are dealing with these scenarios as well and you find my solution interesting, please read further and learn how to get it. It is completely FREE and will take your M Code to the next level Smiley Happy

 

Let’s see how to get there.

 

 

What you need to do to get your Power Query Editor

 

Creating your own M editor in Notepad++ is not complicated and can be seperated in the following steps:

  1. Download Notepad++
  2. Creating Keywords highlighting via the GUI of Notepad++
  3. Adding parameter hints and help texts by using a specific XML file

I will guide to create your editor. Stay tuned Smiley Happy

 

1. Download Notepad++

Of cause you need to download the free tool Notepad++ (e. g. from here).  I installed the first version („Take this one if you have no idea which one you should take„) on my machine.

 

2. Creating Keywords highlighting via the GUI of Notepad++

After you installed the software you need to do some modification in Notepad++. Go to Language and choose Define your language…

03.jpg

 

 The form for defining the user defined language opens. Choose Create New… and give your language a name. I called it M.

04.jpg

 

 Now you have to define special keywords and their styles. Go to tab Keywords Lists and fill the first group with all the functions from Power Query (Click this link ssbi-blog.de to get to my personal blog, where I provide this list of functions for you). Go there and click the link list of functions for keywords highlighting). Mark everything in Excel (ctrl + a), then copy (ctrl + c), move to 1st Group in Notepad++ and paste all the function into that box (ctrl + v). The result looks like this:

 

05.jpg

 

Then press Styler and style the keywords as you like it. I decide turn all my function keywords in deep blue and bold letter.

 

06.jpg

 

 Take care also to select the check box for prefix mode. That way your keyword only get styled, when it is used as a prefix. And now comes one of the most important steps: Don’t ignore case Smiley Happy

 

07.jpg

 

After we have defined all functions as keywords, we need to add some more keywords. The list of keywords is not complete. Feel free to add specific keywords and adjust the styles as you like it.

 

08c.jpgFor your convenience copy the following words into the groups:

 

Group 2: let in

 

Group 3: each

 

Group 4: if then else

 

Group 5: try otherwise

 

Group 6: #table #date #shared #sections; Because the elements in the 6th group are used like function, take care to activate Prefix mode.

 

Now we’re coming to comments:

 

09c.jpg

 

Use Styler in the same way as you did it before. I choose deep green as color for comments. To select Allow folding of comments does exactly what it says. It allows you to fold comments together, if you want to focus on the source code only.

 

The final step is to define the delimitors. Select the operators and deliminators tab.

  

10.jpg

 

For your convenience copy it from here:

 

Operators1:  „>, ; = & ( ) [ ] { } @ ! ? => .. … = < > <> + = * / <= >=</ #

 

Operators2:  and or not

 

There is no save button. So just click the cross in the upper right corner to close the dialog.

Adding styles by pushing the button Stylers is recommended… I suggest to use red color for Operators.

Now my code already has intellisense and highlights keywords. But I am still missing parameter hints and help texts.

 

3. Adding parameter hints and help texts by using a specific XML file

Adding parameter hints and help texts is easy for you. I already created the necessary XML file for you. On my personal blog SSBI-Blog (follow this link) you find several links to download several files. I provide two files for auto completion and help texts: One in German and one in English. Download one of the following two files English auto completion file or German auto completion file from the very end of this article and put it into your APIs folder of Notepad++. On my machine I find it under …

 

C:\Program Files (x86)\Notepad++\plugins\APIs

 

Now restart your Notepad++, press ctrl+n for a new window and choose “M” as your language (as you can see in the following screenshot).

 

12jpg.jpg

 

 Now have fun writing M-statements much easier than before Smiley Happy

 

TAKE CARE: The name of the new language and the name of the XML file has to be the same. If you decide to use something different from M, keep this in mind. 

 

After each update of Power BI Desktop I will provide you with new files following this link. Then just do 2 things:

  • Download the List of functions for Keywords highlightings. In Notepadd++ go to Language –> Define your language. Then go to tab Keywords Lists, delete all old Keywords from 1st group and put in the new Keywords from the List of functions for Keywords highlightings.

 

05.jpg

  

  • Download the XML file again and replace the old file (in folder …plugin\APIs\) by the new. That’s it.

  

I hope this will help you to get a deeper understanding of the M language. Sharing is caring. If you liked this article, feel free to share it.

 

Regards,

Lars 

Comments
by cnewell7 Frequent Visitor
on ‎03-23-2017 12:37 AM

Hi Lars, this is hugely valuable but I'm afraid beyond the reach of most folk I work with

Any chance of having intellisense built in sometime soon. I asked Miguel L about this a while ago and will pester again!

by LarsSchreiber Member
on ‎03-23-2017 12:44 AM

Hi @cnewell7,

 

thanks for your feedback. I know they are working on it. My last info was: Integrating Intellisense will take at least several more months. But please ask Miguel again, so they see how much required this feature really is.

 

Thanks and have a great day,

Lars

 

by cnewell7 Frequent Visitor
on ‎03-23-2017 12:53 AM

Cheers Lars, you have absolutely nailed it here:

 

It is a pain to write custom M code. The Power Query Advanced Editor comes without intellisense (auto completion), no parameter hints, no syntax highlighting, no help texts, which explain what the functions do, no nothing. The fact, that M is case sensitive doesn’t make it easier at all.

 

Juggling to and from a reference guide is a complete nightmare

 

more generally speaking I think there needs to be the equivalent of DAX studio built into Power BI to assist with authoring, formatting and debugging both M and DAX. That would be a quantum leap for serious users

 

Anthony 

by dickovan Frequent Visitor
on ‎03-23-2017 07:59 AM

This is brilliant and something I will definitely use. For the keywords and styles, could you just export the language definition so it could be imported without the copy and paste steps?

 

The step for the tooltips would still be necessary.

by LarsSchreiber Member
on ‎03-24-2017 07:09 AM

Hey @dickovan,

 

thanks for your positive feedback Smiley Happy

 

I was playing around with the file, which defines keywords and styles but I had trouble using it. For some reason it didn't work as expected. Because you only have to define keywords and styles once in a lifetime and only have to update the list of functions (which is one copy and paste step) once a month I decided this file is not worth the effort. Hope you agree Smiley Happy

 

Regards,

Lars

by DavidMoss Member
on ‎04-15-2017 11:25 AM

Hey @LarsSchreiber, I have just been using your tool in Notepab for some M coding...pretty cool and thanks for sharing. David

by LarsSchreiber Member
on ‎04-15-2017 11:54 AM

Hey @DavidMoss 

 

Hope you're well :-) Glad you like my editor. Hope you'll have great Easter holidays. Cheers, Lars 

by kschaefers Regular Visitor
on ‎05-02-2017 08:41 AM

Hi @LarsSchreiber, this is really cool! thanks a lot. Is it possible that you share this configuration as an xml file that we can simply import into Notepad++? That would make it even easier.

Cheers, Konrad

by emudria Member
on ‎08-17-2017 05:11 PM

@LarsSchreiberAwesome work . Thank you.