+ Reply to Thread
Results 1 to 22 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: 1167
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: 1168
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: 1178
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: 1167
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: 1124
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: 1179
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: 1151
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: 1131
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: 1132
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: 1134
Size:  93.2 KB

    Name:  11-language.jpg
Views: 1107
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,494

    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
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,090

    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
    49,996

    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
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,090

    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.

  16. #16
    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)

    @AliGW
    So put this line to the top of the first post, if you can, please: (because I can't do that)
    Credit: [URL="https://ssbi-blog.de/technical-topics-english/power-query-editor-using-notepad/"]Lars Schreiber[/URL]

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,090

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

    OK - done. Please could you share a link to the Matt Masson source, too? Thanks.

  18. #18
    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)

    it doesn't work

    you copied noparse, maybe try: https://ssbi-blog.de/technical-topic...using-notepad/ with URL from menu

    Lars Schreiber

    Matt Masson

  19. #19
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,090

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

    I copied what you had typed, Sandy, but have corrected it now.

  20. #20
    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)

    So you did it wrong
    I copied exact text from above and see here;
    Credit: Lars Schreiber

    Name:  credit.jpg
Views: 264
Size:  27.1 KB

    there is link to Matt also - post#18

  21. #21
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,090

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

    Whatever you say, Sandy.

  22. #22
    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)

    Yeah, whatever - If something is to be done well - do it yourself

    it looks ugly now, but thanks for tryin'
    Last edited by sandy666; 06-01-2018 at 01:46 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Sub-Forum for Power Query & Power BI?
    By AliGW in forum Suggestions for Improvement
    Replies: 7
    Last Post: 08-26-2018, 05:25 PM
  2. Replies: 0
    Last Post: 04-05-2018, 01:16 AM
  3. error in power query & power pivot
    By Baldev Kumar in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 01-03-2018, 01:34 AM
  4. [SOLVED] power query editor - unpivot?
    By pccamara in forum Excel General
    Replies: 5
    Last Post: 09-07-2016, 10:26 AM
  5. Replies: 0
    Last Post: 05-16-2016, 02:56 PM
  6. [SOLVED] How to set a power point presentation as an object (from the excel vba editor)
    By Jonas1503 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-09-2014, 08:08 AM

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