I'm super excited that Power Query Intellisense and Highlighting features are finally here in Excel! It's been a long time coming, with these features debuting in Power BI Desktop months ago.
Where do Intellisense and Highlighting Show up?
Let's look at all three places that Intellisense and Highlighting are now exposed in Excel's Power Query.
Intellisense and Highlighting in the Formula Bar
The first thing you should notice about the image below is that Excel now adds colour highlighting to the text in the formula bar. "Text" values are shown in red, keywords in blue, and data types in green:
In addition, you can see Intellisense showing in the picture above as well. In this case the cursor is immediately after the opening parenthesis character. Unlike in the past, we now get a nice syntax popup box that tells us what needs to come next in this function.
Intellisense in Custom Columns
This is the place where I really wanted to see this happen. Here's a look at Intellisense working live as I started to build a column using a Text function:
Pretty sweet, as this actually starts to display the options.
(Now, to be fair, in my build of Excel, this feature only seems to consistently work if I rename the column first, then tab into the formula builder. If I just click straight into the formula, bypassing the tab order, it doesn't seem to kick in.)
Syntax Highlighting in Custom Columns
How about syntax highlighting? The formula here is only meant for demo purposes, but you get the idea of how it can look:
Blue keywords for if, then, else. Hard coded values (like 3) show up in green like data types, and text is showing in red.
Intellisense and Highlighting in the Advanced Editor
And finally, Intellisense and highlighting work in the Advanced Editor as well. Here's a look at the syntax highlighting:
And here's what happens when we start manually adding a new step, which kicks in the Intellisense in the Advanced Editor window:
How do you get these features?
First, you'll need to be on Office 365. Sorry Excel 2016 and 2019, but my understanding is that these new features are only coming to subscription users. (That's another reason that - in my opinion - you should never buy another 4 digit numbered release of Excel ever again.)
If you are on subscription, you get them with your regular updates. This feature set hit the Insider build channel last month in version 1907. It's currently also been deployed to those on the Monthly Targeted channel running version 1907, build 11901.20080 or newer.
If you're not on the Monthly Targeted channel, you'll need some patience, as it's coming. Just keep checking those updates!