Yesterday, Microsoft released the June 2016 Power Query update. Even though there are only four items on the list of new features, some of them are quite impactful.
What’s new in the June 2016 Power Query Update
The four new features are:
- Conditional Columns
- Column type indicator in Query Editor preview column headers
- Reorder Queries and Query Groups inside Query Editor via drag and drop gestures
- Query Management menu in Query Editor
Microsoft has a blog on this here, but let me hit these quickly in reverse order to give my comments as well:
Query Management Menu in Query Editor
Honestly, to me this is kind of a throw away waste “button for the sake of a button” kind of feature.
Does it make things more discoverable? Maybe. But we can get to all these features by right clicking the query in the Queries pane on the left of the editor. Personally, I would have rather seen them give me a feature to “pin” the Queries pane open and set that as a default, as I find the navigation from that area much more useful:
Reorder Queries via Drag and Drop
This is great… so great in fact, that the only real question is why it hasn’t worked in the past. Time & resources is the answer, but it’s now working the way you’d expect it to work.
PS, if you don’t know how to group your queries, right click on one, say “Move to Group” and select New Group. Pretty handy for keeping things organized.
Column Type Indicator
This is BY FAR the most important of the upgrades. The reason is that this has been a deadly area of weakness since day one. If you’ve ever been burned by an “any” data type, you know why. And if you haven’t… hopefully this will help ensure you don’t.
We can now plainly see which columns have been defined with each data type:
Notice how easy it is to tell that the “Client, Task and Notes” fields are text (as shown by the ABC icon in the column header.) Hours is a decimal number, rate is a whole number, and Date… is undefined. That one needs attention as indicated by the question mark. Very visual, and very badly needed for a long time. This one feature is, in my opinion, worth the upgrade.
This is also a pretty cool feature, as it lets a non-coder build an if then else (if) statement. Full caveat here: this is the image from the official Microsoft blog, not one of mine, but it shows you the general idea:
As cool as this is, there are some issues here:
- You can only feed out full columns as outputs, not formulas/equations. So if I wanted to check a column and return [Hours]*[Rate] in one case and [Hours]*1.5*[Rate] in others, it won’t work. (Instead I’ll get text.) To do that you’ll still need to write your formulas manually.
- You can’t provide IFERROR style logic to check if something errors and react accordingly. To do that you’ll still need to create your own custom column formula using the “try otherwise” formula.
- Assume you created a custom column using the “Add Custom Column” button, and manually wrote your “if then else” formula. You then committed it and want to change the logic, so you click the gear icon in the applied steps window… and you’ll be taken to the Conditional Column interface shown above, not the original window where you can create more complex logic. So if you want to modify that formula to be more complex than this new interface allows, you’re now going to have to go to the Advanced Editor window. I have suggested to Microsoft that they need a button to return is to the previous interface for this scenario.
Despite the shortcomings, we should recognize that this is a great new feature. You can test if one column compares (match, doesn’t match, greater than, etc) another column or specific value without having to manually write any M code formulas. You also aren’t obligated to feed out a column’s value, but rather can feed out text or values too. So as long as your logic needs are fairly simple, you can use this feature.
Download the June 2016 Power Query update
You can pick it up from Microsoft’s site here: https://www.microsoft.com/en-ca/download/details.aspx?id=39379
Also, I’ve started holding on to the previously released installers should you ever need to regress to a prior version. You can find the installers I have in my forum here: http://www.excelguru.ca/forums/showthread.php?5745-Installing-Power-Query