+ Reply to Thread
Results 1 to 24 of 24

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

  1. #1
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    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: 4003
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: 3935
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: 3865
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: 3964
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: 3831
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.

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    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: 3970
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: 3862
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: 3823
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: 3960
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
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

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

    Required files:
    Attached Files Attached Files

  4. #4
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

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

    Example: .

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

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

  5. #5
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    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
    Posts
    9,647

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    78,938

    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!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  8. #8
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    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 7/10/13/16/365 (PC ver 2310)
    Posts
    52,906

    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
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    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,085

    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
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    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,085

    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
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    78,938

    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
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    78,938

    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
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    78,938

    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
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    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: 2885
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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    78,938

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

    Whatever you say, Sandy.

  22. #22
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    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.

  23. #23
    Registered User
    Join Date
    12-14-2022
    Location
    london
    MS-Off Ver
    2016
    Posts
    3

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

    To create an editor for Power Query using Notepad++, you will need to perform the following steps:

    Install the Power Query M Language extension for Notepad++. This extension allows Notepad++ to recognize and highlight the syntax of the Power Query M Language.

    Open Notepad++ and go to the Plugins menu. Select the Power Query M Language extension and click on Install. This will add the Power Query M Language extension to the list of available plugins in Notepad++.

    Once the Power Query M Language extension is installed, you can create a new Power Query script by going to the File menu and selecting New. This will open a new editor window in Notepad++.

    In the new editor window, type your Power Query script using the syntax of the Power Query M Language. The syntax will be highlighted and automatically formatted by the Power Query M Language extension.

    When you are finished writing your script, you can save it by going to the File menu and selecting Save As. Give your script a name and save it to a location on your computer.

    To run your Power Query script, open the Power Query editor in Excel or another program that supports Power Query. In the Power Query editor, go to the Home tab and click on the From File button. This will open a dialogue box where you can select your saved Power Query script.

    Once you have selected your script, click on the Open button to load it into the Power Query editor. You can then run the script by clicking on the Apply & Close button. This will execute your script and display the results in the Power Query editor.

    By following these steps, you can create and edit Power Query scripts using Notepad++ and the Power Query M Language extension. This can be a convenient way to work with Power Query scripts, especially if you prefer using a simple text editor like Notepad++.

  24. #24
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

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

    I don't see any the Power Query M Language extension

+ 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