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.
Conditional Columns
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
13 thoughts on “June 2016 Power Query Update”
Thanks for the heads up Ken, will download tomorrow
Interesting you should mention regression. Had a user approach me last week saying the queries he authored in 2013 suddenly 'blew up' on 2016. These are queries which he stated had worked reliably 100s of times in 2013
The best advice I could offer him was to just re-author the queries from scratch in 2016 by copying and pasting the M code and/or upgrade to 64 bit, but these were very small datasets
Have you seen this phenomenon? I'm concerned that I'm finally getting buy-in from folk on 2013 and when they migrate to 2016 the world will fall in on them
I guess the first question I'd have to ask is if he's using an MSI or deferred release subscription version of 2016. In either case, they won't be as current as the 2013 frequent releases. That could be part of the issue if he's taken queries written using say the Feb 2016 release and put them into the original (non-subscription) Office 2016 release (using last Aug Power Query functionality.)
Another issue may be that it is actually the data set that isn't available, but I'm sure you checked that out.
How backwards is it that Excel 2010 and 2013 users can have features that Excel 2016 users can't? Makes no sense. MS get it together.
Hey Phil,
I'd be somewhat careful with that statement, as there is actually a lot going on here, with a lot of pressure from external sources that have driven Microsoft this way.
To be fair, Excel 2016 subscription customers are now receiving updates regularly. In fact, I'm on the early release channel (an option for any O365 customer), and I've had these features in Excel 2016 for 2 weeks. So in fact, they are pushing earlier to 2016 than 2010/2013. (To my memory, this is the first time we've got an update in Excel 2016 before 2010/2013, even though we've know that is the logical direction for this to go.)
With regards to 2010/2013, I believe the formal licensing says that you actually are supposed to have software assurance in order to download and install the updates. If you do, then you're on side, as that is essentially the "subscription" price. For those not on SA though... Microsoft actually can't check. So in fact, those users are not onside, but can download it anyway. That's probably where the biggest licencing/technical failure is here.
The real issue comes with the non-subscription version of Excel 2016. There may be software assurance in play, but corporate IT (not Microsoft) -by buying the non-subscription version - has specifically decided that they do NOT want the updates. (Some reasons for this are good, others are just not.) Ultimately we need to recognize that IT was given a choice over how current they wanted their user base to be, and IT made that choice to be behind. This is the first version of Excel where that disparity has really become apparent, and I hope that it ends up driving IT to actually go subscription in the future. (They HAVE to do it for your anti-virus, they just need convincing that it is also the right thing to do for productivity software.) Despite saying that, there will almost always be shops that want to stay with fixed versions due to compatibility issues with other software.
The other caution I'd throw out here is this... if we complain too much then the logical thing for MS to do is to actually stop delivering updates to 2010/2013 at all. I mean really, Excel 2010 is Excel 2016's biggest competitor. It makes NO sense at all for them to do what they're doing by delivering these updates to legacy versions.
I don't disagree that it's frustrating for the end users caught in the Excel 2016 MSI world. But if Microsoft were to provide constant updates to the non-subscription versions, then why would anyone purchase a subscription? In addition, I'd bet that it would actually force some companies to stay on 2010/2013 to control the updates (by blocking you from installing them.)
The biggest issue we are facing here is that we have over 25 years of history of buying perpetual licenses for Office software, and we need to break that expectation as a model. Microsoft is trying to straddle two worlds of delivery here, and to be fair, they probably won't be able to make everyone happy. Once we cross to the subscription world though, so much pain will go away. I for one can't wait until I never have to ask my course attendees what version of Excel they're actually on. It will happen one day, but is still a way away yet.
Thank you, thank you, thank you for the old installers. This latest update actually fixed the problems I was having, but I've actually had a couple of months now where Power Query was working poorly if at all, after several months before then of perfectly stable running. Having the old installers available is much appreciated.
No worries, Matthew. Been meaning to do it for a while. Worst part is that I wasn't in the habit of keeping them!
Hi Ken,
I agree with you that it's tricky. At home I have a discounted license because our company does have software assurance, and pays a monthly fee to MS for it. We're not on O365 at work either, but you're right that for whatever reason IT does like to disable Windows/Office update. I do have local admin so I can install updates, though, and try to keep everyone up to date but it's not easy.
It's really rough because updates to Power Query/Power Pivot that integrated them into Excel 2016/2013 means you can access them from VBA, you don't need to install them afterwards separately, and everyone has them. The downside is these feature improvements don't even make it to you.
I personally do not understand why companies are so hesitant to move to subscription when they are paying a monthly fee for software assurance already. But I think MS needs to really get their platform and feature set standardized, and have options for those using Office at work to have discounted subscriptions (or include it for 1/2 computers at home) at home as well because $15.20/month so I can use Office at home is rough for a lot of people (per user).
Perhaps if these features became available in Office 2016 at the same time, that would be adequate... Power BI Desktop has had these features (for free) for a few months already, and I wasn't even sure if MS was going to bring them to PQ for Excel.
Cheers Ken
He is on corporate Office 365 subscription, same as me. However, I can get the latest updates because I have a standalone PC off network which I requested because of IT policy to prevent you having the latest stability updates (WTF)
My head is in a bit of a spin with all the different versions but I'm keen to make anything that I have work on 2016 even if it means rebuilding it. Then I can disregard 2013 completely so I'm not hopping back and forth between the two
BTW I am trying to using Power BI desktop as much as possible now in preference to Power BI Excel. Not that there is anything wrong with the latter but the all round performance, stability and responsiveness of PBI is just far slicker than power querying or power pivoting via Excel. I want to move to be in a position where I point my thin client workbooks at the PBI data model to get their data using 'Analyse in Excel'. A few glitchy things happening with this but I've no doubt it will get better
So I have this very crazy project at work that was requiring me to build a convoluted If Then Else. The thought of just doing it by writing code was daunting. I check the blog - like I do every morning-, see this new post and there it is. I wrote the 24 step code in less than a minute I think, looked at it and realized I could cut it down to just 12 lines in the PQ editor window.
I am CERTAIN I would have made 47 mistakes in the 24 iterations I would have tried to write by typing. So while you may not have authored the code and scheduled its insertion into PQ, the fact that you saved my bacon by writing the blog post added to the excellent training from you and Miguel last year and I am your most loyal reader.
Awesome, Carl. Nice to know the blog and training are both having an impact. Thanks for posting!
🙂
Hi Ken,
Have you seen these "Action" functions before?
https://msdn.microsoft.com/en-us/library/mt708835.aspx
Note all of the additional functions on the sidebar, not just the four listed in the main section of the page.
Looks interesting!
Hey Alex,
Anthony Newell brought them to my attention a couple of weeks back, but I haven't had time to look at them. Based on the WebMethod.Post, I'm kind of hopeful that this is a method to actually write our Power Query output somewhere. If it is, then maybe it's a precursor to being able to also write to a database. That would VERY VERY cool.
Ken - et al.
§ having trouble setting up conditional columns with multiple boolean operators using the 2.35 version.
§ getting an error statement "object reference not set to an instance of an object"
§ the bug was called into our Microsoft friends - until this is fixed, I'm back to 2.33 version.
Thanks Drewbbc