THE SELF-SERVICE-BI BLOG

Wir lieben Self-Service-BI mit Microsoft Power BI

Technical Topics (english) /

Get your own Power Query Editor using Notepad++

Preface

This post is not new. I published the first version on how to create your own Power Query Editor already in November 2015 on my old blog www.powerbi-usergroup.de. Many things changed since then. First: I changed my blog and created THE SELF-SERVICE-BI BLOG. Second: Many of the details I showed in my older post to create your own editor for Power Query with Notepad++ didn’t work anymore. Several circumstances had changed, so I had to change the post as well. Now let’s go. Get your Power Query Editor 😉

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

Finding the right function using the Power Query Editor
Finding the right function using the Power Query Editor

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

Working with highlighted syntax for Power Query M code
Working with highlighted syntax for Power Query M code

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 🙂

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 🙂

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…

Define your own language
Define your own language

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

Create a new language: M
Create a new language: M

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. (I provide this list for you at the very end of this article. 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:

Add all functions as keyword
Add all functions as keyword

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

Style your keywords as you like it
Style your keywords as you like it

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 😉

M is case sensitive: Don't ignore case in Power Query
M is case sensitive: Don’t ignore case in Power Query

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.

Define keywords for the Power Query language M
Define keywords for the Power Query language M

For your convenience copy the following words into the groups:

Group 2: let in

Group 3: each and as error true false is meta not or section shared type

Group 4: if then else

Group 5: try otherwise

Group 6: #binary #date #datetime #datetimezone #duration #infinity #nan #sections #shared #table #time ; Because the elements in the 6th group are used like function, take care to activate Prefix mode.

Now we’re coming to comments:

Adding tokens for commenting
Adding tokens for commenting

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.

Defining the delimiters
Defining the delimiters

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. At the very end of this article 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).

Selecting the M language in Notepad++
Selecting the M language in Notepad++

Now have fun writing M-statements much easier than before  😉

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 at the end of this article. 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.

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

See you next time and keep in mind: Sharing is caring. If you liked this article, feel free to share it

Greets from Hamburg,

Lars

Download files

8th of August 2018

This update contains 2 new functions:

  • Cdm.MapToEntity()
  • Python.Execute()

List of functions for Keywords highlighting

English auto completion file

German auto completion file

Attention: If these links prompt you to log in with your Microsoft account, please try Microsoft Edge. OneDrive is a bit bitchy at the moment, although I have released the link for everyone (without the need to log in).

Neue Kommentare

Reader Interactions

Comments

  2. Thanks for this, it will be useful. I am not able to access the English completion file. When I click it, I get an empty browser page. Can you please send another link if possible?

    Antworten

  3. Hi, I have a Notepad++ v7.6.6 and in case anyone had the issue with autocompletion here’s the answer: move the file M.xml to C:\Program Files\Notepad++\autoCompletion folder instead of
    C:\Program Files\Notepad++\plugins\APIs

    Antworten

  4. Hi Lars

    I am very happy, I have discovered the solution.

    At the end you were right, there was a shortcut (Ctrl +Mouse Double click) over a parenthesis in order to get the scope of parenthesis. The issue is that if the scope of the nested parenthesis is more than a line it doesn’t works… this is the default option. But Eureka! this can be configured! Just go to Settings+Preferences+Delimiter and check in „Allow on several lines“.

    These tip is super as it works for all the languages!!!

    Yours
    Javier

    Antworten

  5. Thank very much. This is a great help to code in M!

    I wonder if there is the possibilty to mark the matching parenthesis in the code, and the code between them.
    I have seen Ctrl+B and Ctrl+Alt+B dor brackets but nothing for parenthesis!
    Is it possible to change any style in order to get this facility?

    Thank you very much in advance for any hint or suggestion
    Javier

    Antworten

    • Hi Javier,

      thanks for your feedback. Honestly I am not a heavy user of Notepad++ shortcuts, but here is a list of all documented shortcuts of Notepad++, so maybe you’ll find what you’re looking for?!

      Cheers,
      Lars

      Antworten

      • Thank you Lars

        Maybe I didn’t explain properly. I am not looking for shorcuts. In the matter of fact there’s no one for „parenthesis“ (there is one for matching braces Ctrl+b)

        I would like to look for the „matching parenthesis“ in M. I cannot see how to do it. I thought that just defining an style in the user-defined counfiguration for M then I could. For example there you can do something similar for comments, with the open comment /* and close comment */

        In M there is sometimes a lot of levels of identation and it is no easy to find out the scope of a parenthesis….

        Anyway, thanks a lot for sharing your work
        Javier

        Antworten

      • I’m saying the need still exists but the audience is certainly smaller than it once was.
        I can see where, if you’re not actively using Notepad++, it would be more difficult to keep the M configuration files up to date.
        I can likely figure out how to update the M.xml file myself from the NPP docs.
        If you could share how you’re tracking the current list of keywords and functions (and generating the help text) that would certainly help.

        Antworten

  8. This article is great and has helped me develop in Power Query M. There is an error.
    This query:
    „Hello World#(cr)#(lf)Hello Again*/*CommentHello*/“

    will return

    Hello World
    Hello Again*/*CommentHello*/

    However the user-defined language used here will put the /*CommentHello*/ section in light green to indicate that it is a comment.

    This is an issue for me because I have a variable that needs to be equal to the string */*

    Thank you again

    Antworten

    • Hi Johan,

      happy, that it makes you write plane M code in the future 🙂 But there are even better news. In the very near future, Power Query will have a native editor, at least in Power BI Desktop, so mine won’t be needed anymore.

      Cheers,
      Lars

      Antworten

  10. Hi Lars,
    there is an error in the new version of the english autocompletion file. Description is still good, but all parameter hints are identical, like this:

    The german version is good.
    Hope you can fix this easily. Anyway, this is still fantastic!!

    Cheers,
    Clemens

    Antworten

  12. Hi Lars,

    you can also export the whole definition of a User Defined Language into a single XML. This way the users would not need to do all the steps describes in this post manually but could simply import the UDL XML file.

    maybe you could also add the UDL definition file here, this would save a lot of time for the users 🙂

    regards,
    -gerhard

    Antworten

  15. Maybe you should add that in order to get the hints you also have to enable
    Settings -> Preferences -> Backup/Auto-Completion -> Function parameters hint on input

    Antworten

  16. Thank you!! Very helpful!! I am using this and wanted to share one tweak. I had an issue where Notepad++ was confusing a text string containing a URL as a comment.
    My tweak was to add quotes as a delimiter (Operators and Delimiters tab). It changes both the delimiter and the delimited text to the new color.

    Antworten

  17. Amazing job, thank you!
    I am attending a PowerBI course with my colleagues, today I’m going to astonish them with this Notepad++ trick!

    Antworten

  18. Hi Lars,

    This is a superb facility. Thank you.
    Like Knoll in his message on April 2nd 2017, I am not seeing the Help Text for the selected functions.

    Could this be related to the version of Notepad++ that I am using? There was a 64-Bit version already on the PC (v7.3.3) and I put the M.xml file into the relevant folder in the Program Files folder.

    Look forward to hearing from you when convenient.

    Many thanks.

    Antworten

    • Hi Lars,
      thanks for this!! I loved Notepad++ and PowerQuery already, but now even more…
      I had the same issue as Kroll and Ted. I just copied the xml file to \Users\*username*\AppData\Roaming\Notepad++\plugins to solve it.
      Thanks again,
      Clemens

      Antworten

  19. Great post, but does anyone else find it nigh on impossible to get the reserved words out of the link for the CSV that opens in Excel online? Can’t save, can’t copy and paste more than a handful at a time, it is really painful!

    Antworten

    • Hey Jeremy,

      thanks for your feedback. Didn’t hear about the copy/paste problem before. I fixed it, so you can download the complete file. Hope that helps 🙂

      Regards,
      Lars

      Antworten

  20. Lars,
    This posting very much helped me write in M. Thank you very much. Please tell me what you do to highlight a user defined token, such as user-defined function & parameters. Thanks,
    Dan

    Antworten

  21. Hey Lars,
    Thank you for sharing that with the world!
    One question – what could be the reason for help texts now showing up?
    I followed your instructions, and the list of functions shows up when I start typing, but when I select a function and open the left bracket nothing happens.
    What could be the reason for that?

    Antworten

    • Hi Kroll,

      you’re welcome 🙂

      The list of functions comes from the values you put manually in the 1st group box in the Keywords Lists tab.

      You don’t see help texts and I guess, that you don’t see parameter hints either, right? Then something doesn’t work with the xml file. Try to find the ansswer to following questions:
      – Did you put it in the right folder of Notepad++?
      – If so: Does your xml file have the same name as you named it under user language in notepad++ (in my example „M“)?

      Please give me feedback, if this helped you.

      Regards,
      Lars

      Antworten

    • Hey Denis,

      thank you 🙂 No, I am not planning this for DAX for several reasons. On the one hand the DAX editor already supports intellisense, synthax highlighting and help texts. What are you missing? On the other hand I honestly have no clue how to get all the necessary meta data of the DAX synthax out of Power BI Desktop.

      Regards and thanks for you feedback,
      Lars

      Antworten

    • Hey Mike,

      thank you very much. Happy when it helps 🙂
      MSFT is already doing a great job on building the whole Power BI Platform. I think implementing an „integrated“ editor is much more work than building this one in Notepad++. I know they are working on it and in a couple of months my workaround can be replaced by an integratd solution. I would love to see that soon 🙂

      Regards,
      Lars

      Antworten

      • Hey Mike,

        I am very happy to help 🙂 To highlight your custom function simply add it to the „Keywords lists“, 1st Group. That highlights the function (not the parameters). In order to get parameter hints open the „Auto completion files“ I provide you with, with a XML editor (e. g. Notepad++). For each function you see different line of code. Try to adapt that for your custom function. For better explanation see this link: http://docs.notepad-plus-plus.org/index.php/Auto_Completion
        The section „How to create keyword auto-completion definition files“ should contain any necessary information for you.

        Hope that helps,
        Greets from Hamburg,
        Lars

        Antworten

Schreibe einen Kommentar

Mit Deinem Kommentar, speichere ich auch den Zeitpunkt der Erstellung des Kommentars, Deine E-Mail-Adresse und, wenn Du nicht anonym postest, den von Dir gewÀhlten Nutzernamen. NÀheres hierzu findest Du in meiner DatenschutzerklÀrung. Danke! With your comment, I also save the time of creation of the comment, your e-mail address and, if you do not post anonymously, the user name you have chosen. You can find more information on this in my privacy policy. Thank you!

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

Send this to a friend