“Excuse me, sir, you don’t need 20 men, you need Miller Banfield.
He’s an expert with explosives Excel, sir. He’s probably the best in England.”
That’s right folks, after prowling the comment threads for months now as one of the most insightful contributors, Colin Banfield of BIExcel.com is taking the plunge and sharing a full guest post with us. This is indeed a very positive development for us all 🙂
Today, Colin is giving us more info on using Notepad++ to write PowerPivot formulas. Take it away Colin 🙂
Using Notepad++ as an editor for creating, reading, and troubleshooting DAX formulas
Rob recently wrote an article about the difficulty he experiences editing complex formulas in PowerPivot, even though he helped to create editor used in the Measure Settings dialog box. You can read Rob’s article here https://p3adaptive.com/2010/03/27/two-observations/. In the article, Rob noted the following as shortcomings of PowerPivot’s built-in editor:
· Can’t indent formulas
· Can’t save formulas
· No find and replace option
· Can’t change the font size
To address these issues, Rob has been using Notepad as his PowerPivot formula editor. In the above-mentioned article, Rob solicited opinions on alternative text editors. Some folks, including me, suggested Notepad++. Notepad++ is a free text editor that might be viewed as Notepad on steroids. However, the point is not that Notepad++ has more features than Notepad – it’s that you can customize the program to work specifically with DAX formulas. The purpose of this article is to explain how the customization is done, and the many benefits you gain from the effort.
You can download the latest version of Notepad++ from here https://notepad-plus.sourceforge.net/uk/site.htm. During the installation, I strongly suggest that you don’t check the option to bypass using the %APPDATA% folder for storing Notepad++ settings (the option is unchecked by default is used mainly for portable installations). If you have already installed the program and checked the option during installation, I recommend that you completely uninstall the program (including everything under the Notepad++ installation folder) and start over.
Figure 1 shows the Notepad++ main window. The file manager window on the left is a plug-in that you can install through the Plugins menu. The plug-in is called Explorer. The built-in file manager is useful when you want to quickly retrieve saved formula files.
Figure 1 – Notepad++ main window
The first step in customizing Notepad++ is to create a custom language for DAX. The purpose of a custom language is to provide special syntax highlighting for various elements of the language. Choose ViewàUser -Defined Dialogue…The User-Dialog box appears as shown in figure 2.
Figure 2 – User-Defined dialog box
Click Save As… and enter “DAX” (without the quotes) in the Name text box. After you click OK, you will notice that extension (Ext) box appears at the top right of the User-Defined dialog box. Type an extension in this box (txt, dax, foobar, whatever), and save your formula files with this extension. This ensures that when you subsequently open the file, Notepad++ applies the custom language for editing the file. You can associate more than one extension with a custom language by separating the extension names with a space in the Ext box.
After you enter an extension, you can explore the various tabs in the dialog box. My personal setup is as follows (it has changed over time):
· Folder and Default tab – As shown in figure 3.
Figure 3 – Folder& Default tab
The folder open and close keywords are used expand/collapse a block of code. This might be useful for delineating parts of a DAX formula that can be used as intermediate measures in a PivotTable (see figure 7).
· Keyword Lists tab – See figure 4.
Figure 4 – Keyword Lists tab
In the “1st Group” list box, I pasted an alphabetized list of DAX functions.
· Comment and Number tab – See figure 5.
Figure 5 – Comment & Number tab
· Operators tab – See figure 6.
Figure 6 – Operators tab
Naturally, each user will personalize these dialogs to suit his or her taste. Notepad++ saves the configuration after you exit the dialog box. To use your custom language, you must choose it from the Language menu. Custom languages appear in a separate section at the bottom of the menu. Currently (version 5.6.8 at the time of this writing), there is no way to make your custom language the default language. You must select the custom language every time you open Notepad++ and every time you open a new file. This is a very annoying and stupid limitation. If you don’t assign an associated file extension for the custom language, the syntax highlighting for the default built-in language will be applied after you open the file. However, there are many user requests to make a custom language the default language, so it’s possible that the feature will appear in a future version of the product (hopefully, very soon).
Figure 7 shows an example of using your new DAX language to edit a DAX formula. Notice the vast improvements in readability over editing the formula in the PowerPivot Measure Settings dialog box, or in Notepad. The bottom left of the windows displays the language in use.
Figure 7 – Example DAX formula with syntax highlighting, bookmarks (blue blobs) & folding blocks
Pretty cool, no? However, we’re just getting started. The next step in customizing the language is adding function auto-complete, function parameter tooltips and function description tooltips. You heard that right. It takes some time to create the text for the auto-complete XML file, which must have the same name as the language (is this case the name of the auto-complete file would be DAX.xml). You must place DAX.xml in the Notepad++pluginsAPIs folder, and the turn on the appropriate Auto-Completion options in the Preferences dialog box. These options are shown in figure 8.
Figure 8 – Auto-Completion options
I’ve created a DAX.xml file, and anyone interested in using it can download it here:
The next couple of figures illustrate how function auto-complete and tooltips work.
Figure 9 – Function auto-complete
Figure 10 – Function parameter and description tooltip
The only functionality you lose from PowerPivot’s built-in editor is the very useful table and field names auto-complete. You can use the PowerPivot editor to auto-complete table and field names that you paste into Notepad++. In many cases, like that shown in figure 7, you end up reusing the same fields in different parts of the formula – which is then a simple cut and paste operation.
I encourage you to explore the many other features of Notepad++ not covered in this article. Other features I find useful include:
· FileàLoad Session & File Save Session. Because Notepad++ uses a multiple document interface (each document appears in a separate tab), you can save all the formula files associated with a project as a session.
· Bookmarks. You can toggle a bookmark on and off in several ways, for example: 1) click the right-side of the line number in the margin, 2) select a line and enter Ctrl+F2. You can also bookmark all lines containing specific text. Bookmarks are useful if you want to include folding keywords (as shown in figure 7), or comments inside the DAX formula. After you bookmark the folding keyword lines and comment lines, you can delete these lines by choosing SearchàDelete Bookmarked Lines. After you delete the bookmarked lines, you can paste the formula into PowerPivot.
As a final note, I’d like to say that Notepad++ has helped tremendously in managing my calculated measures. With complex formulas, I tend to create many intermediate formulas so that I can see the calculated results in the PivotTable. I don’t want to expose these intermediate calculations to the end user, so before deploying the PowerPivot model, I have to amalgamate the intermediate formulas into a single formula. It’s very unfortunate that PowerPivot doesn’t allow you to hide calculated measures.