Power Query: Get 2016 and Transform

Welcome to our Power Query blog. This week I look at how Power Query is presented in Excel 2016, under the heading ‘Get and Transform’.

It’s time to move on. No, I am not abandoning Power Query, but I have decided that now is the time to move this blog onto Excel 2016.

There are different versions of Excel 2016 and this will determine how often updates are available. For this reason, my version of the ‘Data’ tab in Excel might not look exactly like yours. I am on what is known as the MSI (windows installer) version and my ‘Data’ tab looks like this:

I get updates, but not as frequently as those on the Office 365 subscription. Not everyone on Office 365 chooses to get updates as soon as they are available – there is a version of ProPlus Office 365 which has a Semi-Annual Channel setting. This channel collates updates to be uploaded every six months. Getting the updates less frequently may seem to be a disadvantage, but those of us who have been surprised by new menu arrangements at the start a training session can appreciate the benefits of less frequent updates! In a large company environment, a stable and predictable system can be preferable to having the latest updates immediately.

Microsoft explain more about the channel settings and how to use them here.

To see the version of Office that is installed on my machine, I can go to the ‘File’ tab from any Office product and then choose the ‘Account’ option. Mine is shown below:

When viewing the Excel 2016 menus for the first time, there is a noticeable absence on the Tabs: there is no ‘POWER QUERY’ tab.

Instead, the functionality from the ‘POWER QUERY’ tab is in the ‘Get and Transform’ section:

This name change coincides with a change in how Power Query is accessed – it is no longer an add-in, instead it comes built-in to Excel. Therefore, there is no more ‘losing’ the ‘POWER QUERY’ tab and having to re-check the add-in. Having Power Query as an add-in was great for allowing users to evaluate the tool, but having it integrated makes it more acceptable in corporate environments where add-ins are not always recommended. Power Query is becoming more mainstream.

Despite being in the ‘Get and Transform’ tab, many users still refer to the functionality as Power Query, so it’s worth considering the pros and cons of the change in location, and whether this should go hand in hand with a change in name.

In training sessions for Power Query and Power Pivot, there can be some initial confusion regarding the scope of each tool. There is some expectation that because Power Query has ‘query’ in the title, then it will also allow mapping between queries to create a database, when in fact this is the domain of Power Pivot. This confusion is less likely with a section called ‘Get and Transform’, which is very descriptive. More recent updates of Excel 2016 under the Office 365 subscription take the integration further, to have what Microsoft refer to as a unified Get and Transform, as shown below

This menu structure combines the Power Query and Excel data loading. This avoids the confusion on the current menu structure in MSI Excel 2016 where I have to use ‘Get External Data’ to pull data into my worksheet, and the ‘Get and Transform’ tab to extract data into the Query Editor. More details about the unified ‘Get and Transform’ can be found on the Microsoft website here.

Although moving the Power Query functionality to the ‘Data’ tab integrates it with the other Excel functions, Power Query’s functionality remains distinct in one sense because it uses its own language – M. This presents a problem when trying to find the solutions to any issues encountered in Power Query. Looking up ‘M’ is a non-starter, and looking up ‘Get and Transform’ retrieves more irrelevant results than using ‘Power Query’. Consequently, most information is available under the title ‘Power Query’ and this will persist long after the ‘POWER QUERY’ tab is forgotten. My blog will therefore continue to refer to Power Query!

Want to read more about Power Query? A complete list of all our Power Query blogs can be found here. Come back next time for more ways to use Power Query!