With the current release of Power BI Desktop (September 2018), a feature is available that many Power Query fans have been waiting for for no less than 3 years: Intellisense in the editor. How much this feature was in demand I could clearly see in the past by the visits on my own website, because my article about creating an editor for M in Notepad++ was by far the most read one on my blog. So let’s take a look at this new preview feature together.
Activate the preview feature
At the time of writing Intellisense for Power Query in Power BI Desktop is in preview. To use this feature, you have to unlock it first in the options.
Now that the feature is enabled, where can you take advantage of intellisense now?
„M Intellisense“ only available in the Advanced Editor
There are three different places in Power Query, where you can write M code:
- The formular bar,
- the add custom column window and
- the advanced editor
At the time of writing, the „M intellisense“ feature is only available in the Advanced Editor, but not yet in the other two places. This will change in near future (see here), but for now, you have to use the advanced editor, to take advantage of M Intellisense. Let’s see what the M Intellisense feature delivers in the current version and what we have to wait for.
More than just Intellisense
While Microsoft names this feature M intellisense, it actually has more to offer. Let’s dig into this.
Intellisense is a term for code completion and it completes the M code as follows.
Calling members of #shared
Intellisense let’s you easily call members of the intrinsic variable #shared, which includes all the native M functions, custom functions, results of other queries and defined parameters within the Power Query instance. Type for example
Table. and you get a list of all native Table functions, which you can select by using Tab on your keyboard, or the mouse. You can go the same way to reference results of other queries…
… custom functions…
In all these cases you get a preview of the source code in red letters. But #shared doesn’t contain variables from the current Query. What’s with those elements?
Calling variables from inside the current M script
You can use intellisense to reference variables/ step names from inside the current query, even, if those variables contain spaces, which makes a prefixed sharp (#““) necessary. This will make our lives so much easier, while coding M!
Automatic closing of…
If you enter one of the following characters, the editor automatically creates a closing element for it:
- Square brackets,
- Curly brackets
The prefixed icons
When using Intellisense, you will quickly notice that all elements of the drop-down list have one of two icons in front: The cube and the triangle, the circle and the square. The cube appears for all the native M functions. triangle, the circle and the square indicate all the other elements in the list, which are step names/ variables, parameters, other query names, constants, types, etc.
So much about Intellisense, but there are other features of the editor.
Keyword highlighting highlights reserved keywords from the M language. In the current version of the feature minimum the following keywords are considered.
For some reason I do not know, none of the M functions are highlighted yet. I hope this will come soon.
Parameter hints and help texts
What would an editor be if it did not disclose the parameters for the respective functions and a help texts for its functionalities? We have all had to go through this painfully over the last few years: hundreds of functions with hundreds of parameters and no support from the editor. You can’t remember all those functions and parameters. This has now come to an end. From now on, we are supported by Parameter hints and help texts so that we don’t have to search through the documentation for hours, but can simply „tap on it“.
The box, in which the parameter hints and the help texts appear, sometimes contains a toggle button, as seen in the screenshot below. This toggle button ensures that optional parameters are shown (2/2) or hidden (1/2).
This functionality was not obvious to me and Imke first had to draw my attention to it. This is because even if you toggle to see the optional parameters of a function, those parameters are not prefixed by the word optional.
Comments and numbers
The longer the M scripts get, the more important becomes commenting. This feature is also included in the current version of the M editor:
The only thing I do not like about this experience, is that numbers are highlighted in the same green as the comments, which IMHO makes it harder to read. I would love to see that changed in the future.
Simplified marking of code components
Marking code for copy and past operations was hard in M so far, especially, when your variable had spaces. Now it got pretty easy. Just double click the variable/ step name and it gets marked so you can easily copy and paste it.
To be honest, while checking all the new features of the editor, I didn’t see the Display Options which should be quite obvious until relatively late. They are very useful for keeping track of the code. Display line numbers and Render Whitespace (the dotted lines) can be activated individually or in combination.
Indenting lines of Code
The structure in the code is almost as important as writing meaningful comments. Again, the current version of the editor supports this, if you press ENTER at the appropriate places in the M code. Pressing ENTER after a opening parenthesis:
Pressing ENTER after a equal sign:
So far to what is included in the current version of the editor. But what is missing. I wrote down what I think is still missing.
What’s still missing
Even if I am very satisfied after testing the editor so far, there are a few things I’m still missing:
- As I mentioned earlier, the highlighting of the native M functions is still missing. This should be done as soon as possible.
- We need intellisense not only in the advanced editor, but also in the formula bar and in the add custom column window (will come very soon, as stated here)
- Type declaration for parameter types and function types: When I define a parameter in e. g. VBA, then I get a dropdown for possible types. This does not yet happen, when I define types in custom functions in M, as the screenshot below shows (on the left VBA, on the right M).
4. We need all these great features in Power Query for Excel too 🙂
How do you like the new experience and what do you think about it? Did you find a feature I did not write about? Leave me a comment below 🙂 Regards from Germany, Lars I write my posts for you, the reader. Please take a minute to help me write my posts as well as possible. Thank you 🙂 [yasr_visitor_multiset setid=2]
Lars ist Berater, Entwickler und Trainer für Microsoft Power BI. Er ist zertifizierter Power BI-Experte und Microsoft Trainer. Für sein Engagement in der internationalen Community wurde Lars seit 2017 jährlich durch Microsoft der MVP-Award verliehen. Lies hier mehr…
Power BI meint
I am feeling motivated and now work harder to start my career in Power-BI, hope will get similar success. Thanks for sharing your Power-BI experience.
I see there is no „Format Code“ option also available. In case, I want to reformat my earlier unstructured M Code then it would be so handy to have an option like „Format Code“.
Lars Schreiber meint
yes, good point! I also noticed that, especially because PowerApps has it (see here: https://powerapps.microsoft.com/en-us/blog/automatically-format-your-formula/) . I would be happy if we get it one day.
Thanks for your comment 🙂
Daniil Maslyuk meint
Excellent write-up, Lars! I too noticed that type declarations are not yet supported, so hopefully it’ll be fixed soon.
Also, even if you don’t make any changes in the code and click Cancel in Advanced Editor, you’re still told you’ll lose your changes! Looks like a new bug.