+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 22

How to create an editor for Power Query with Notepad++ (thanks Matt Masson)

  1. #1
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Cool How to create an editor for Power Query with Notepad++ (thanks Matt Masson)

    Credit: https://ssbi-blog.de/technical-topic...using-notepad/

    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:
    • Download Notepad++
    • Creating Keywords highlighting via the GUI of Notepad++
    • Adding parameter hints and help texts by using a specific XML file
    ---
    • Download Notepad++
    Of cause you need to download the free tool Notepad++ (e. g. from here)
    ---
    • 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…

    Name:  01-language.jpg
Views: 1000
Size:  22.0 KB

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

    Name:  02-language.jpg
Views: 1009
Size:  97.4 KB

    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. Open Keywords.txt (attached), select all (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:

    Name:  03-language.jpg
Views: 1013
Size:  108.0 KB

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

    Name:  04-language.jpg
Views: 1008
Size:  123.6 KB

    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!

    Name:  05-language.jpg
Views: 977
Size:  34.4 KB

    continue in next post...
    Last edited by AliGW; 06-01-2018 at 10:02 AM. Reason: Credit to original author added.
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  2. #2
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: How to create an editor for Power Query with Notepad++ (thanks Matt Masson)

    Continuation from previous post

    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.

    Name:  06-language.jpg
Views: 1017
Size:  130.9 KB
    • 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:

    Name:  07-language.jpg
Views: 998
Size:  110.8 KB

    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.
    • Operators1: „>, ; = & ( ) [ ] { } @ ! ? => .. … = < > <> + = * / <= >=</ #
    • Operators2: and or not

    Name:  08-language.jpg
Views: 971
Size:  57.8 KB

    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.
    ---
    • Adding parameter hints and help texts by using a specific XML file

    Adding parameter hints and help texts is easy for you. Get attached M.xml file and put it into your APIs folder of Notepad++. On my machine I find it under …

    C:\Program Files\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).

    Name:  09-language.jpg
Views: 985
Size:  27.8 KB

    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.

    Happy PowerQuerying
    Last edited by sandy666; 05-10-2018 at 01:37 PM.

  3. #3
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Cool Re: How to create an editor for Power Query with Notepad++ (thanks Matt Masson)

    Required files:
    Attached Files Attached Files

  4. #4
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: How to create an editor for Power Query with Notepad++ (thanks Matt Masson)

    Example: .

    Name:  10-language.jpg
Views: 981
Size:  93.2 KB

    Name:  11-language.jpg
Views: 962
Size:  27.2 KB
    Last edited by sandy666; 05-10-2018 at 03:27 AM.

  5. #5
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Cool Re: How to create an editor for Power Query with Notepad++ (thanks Matt Masson)

    If someone want to play with Excel functions, here are keywords
    (xml maybe later )

    Edit:
    I added xml for Excel Functions - XF.xml (eXcelFunctions)
    updated (13/05/2018) Now this is a well-formed xml - I don't know (yet!) why pop-up window sometimes wrok or not but I need to check NotepadPlus wiki.
    hope it will work
    Attached Files Attached Files
    Last edited by sandy666; 05-13-2018 at 02:03 AM. Reason: see edit

  6. #6
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365 ProPlus
    Posts
    9,354

    Re: How to create an editor for Power Query with Notepad++ (thanks Matt Masson)

    WOW! That's fantastic. Thank you so much for sharing it Sandy! This is very useful indeed.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    35,883

    Re: How to create an editor for Power Query with Notepad++ (thanks Matt Masson)

    This will be useful. Thanks, Sandy!
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  8. #8
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: How to create an editor for Power Query with Notepad++ (thanks Matt Masson)

    Thanks sktneer & AliGW.

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    48,848

    Re: How to create an editor for Power Query with Notepad++ (thanks Matt Masson)

    Very useful and informative, sandy, I have made this a sticky
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  10. #10
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: How to create an editor for Power Query with Notepad++ (thanks Matt Masson)

    Thank you Ford

  11. #11
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,083

    Re: How to create an editor for Power Query with Notepad++ (thanks Matt Masson)

    With respect, this appears to be heavily plagiarised (without attribution that I can see) from Lars Schreiber's blog here- https://ssbi-blog.de/technical-topic...using-notepad/
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  12. #12
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: How to create an editor for Power Query with Notepad++ (thanks Matt Masson)

    Most of that, but not all. Any problem? Do you think it would be better to retype in own words if there is ready text? I didn't say I did it myself. Important is one thing: ppl can use it.
    I maded all picture, that's enough

  13. #13
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,083

    Re: How to create an editor for Power Query with Notepad++ (thanks Matt Masson)

    I just think you should give credit to the original author. Without it, the implication is that the work is yours.

  14. #14
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: How to create an editor for Power Query with Notepad++ (thanks Matt Masson)

    Original idea is from Matt Masson but you're right.

    edit: I cannot edit to add credit but you did it

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    35,883

    Re: How to create an editor for Power Query with Notepad++ (thanks Matt Masson)

    Of course you can edit to add the credits! Just do so in another post to the thread, or ask a moderator to add the credit in the original post. I did not notice the mention of Matt Masson until now: you should provide a link to that source, too.

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1