Last week, Dominic Petri shared a link on Twitter that contained some important news from Microsoft: as of the March release of the Office 365 click-to-run version of Excel 2016, the old methods of loading data into Excel will be replaced by the newer “Get & Transform” functionality that is better known as Power Query. All the details are here:
Up to now Excel 2016 users have had to deal with a rather confusing compromise whereby the older data loading functionality existed side-by-side with the new “Get & Transform” functionality on the Data tab of the ribbon. From the March update onwards though the Data tab will look like this:
There are also several other cool new features being introduced, such as the ability to export Power Query queries to .odc files so they can be shared between workbooks, and new UI for managing connections and queries. You’ll still be able to get the old data loading functionality back via the Options dialog if you want.
If you’re a regular reader of this blog you’ll know that I’m a massive fan of Power Query, so naturally I’m really happy to see this happen. I believe it’s a big step forward for Excel in terms of its BI and reporting capabilities and the fact that this functionality is almost identical to the data loading functionality in Power BI means that Microsoft’s overall BI story becomes even more compelling: the skills you learn in Excel are transferable to Power BI and vice versa. On the other hand I can guess this is going to cause confusion for some users – similar to what happened when the ribbon first appeared in Office 2007 – but I don’t think this can be avoided. Ultimately the benefits will outweigh any disruption caused as users have to learn the new way of doing things.
Discover more from Chris Webb's BI Blog
Subscribe to get the latest posts to your email.
Chris
it is always nice to see consistency between Microsoft Product, now maybe it is time to fill the gap of visualization in Excel (mainly cross chart filtering) , they tried with PowerView, but it is deprecated now.
it is funny, i tried a lot of BI solutions, but when a manager ask for a new reports, my first choice is always Excel.
So I would be able to save a PowerQuery as an odc file – but can I use that query both in Excel and PowerBi desktop? I would like to make one query (great tool for translation by the way) and make some reports in PowerPivot, some in PowerBI.
I don’t know – it would be great if we could open an ODC file created in Excel in Power BI Desktop. See also: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/18517885-odc-equivalent-for-power-bi-desktop?utm_content=bufferb12ee&utm_medium=social&utm_source=twitter.com&utm_campaign=buffer
This is a very interesting information, thank you for that post. I also think that it`s a good idea to uniform the Get & Transform experiences and bring “old connections” and “new queries” under one roof.
Due to your article I realized that in Excel`s Get & Transform at the moment it is not possible to create a “live query” to a SSAS Tabular Model or SSAS Cube, the query is per definition always an import … the live query option is still missing whereas it is available in Power BI Desktop.
So obviously we further have to create an old connection to SSAS Databases which are per definition live queries. Do you agree to this or did I just oversee that option in Excel Get & Transform?
There is no option to create a Live Connection in Get & Transform, but there’s no need – the option to create a connection to SSAS that has been in Excel for years does exactly the same thing.
My thought came from the sentence “the old methods of loading data into Excel will be replaced by the newer “Get & Transform” functionality” … and in the screenshot above it seems that the button is missing to create such a Live Query Connection to a cube.
Hi Chris – Can Power Query be used to only get new data? For example could Excel or Power BI Query an api like this:
https://api.com/widgets?created_since='2017-03-24‘
Except instead of a hard coded date, the query sends the date of the last record already retrieved.
In other words, if an api call returns a billion records without filtering, how can Power Query make it manageable and efficient?
Great blog by the way I’ve been lurking for a while. 🙂
Hi Chris – Can Power Query be used to only get new data? For example could Excel or Power BI Query an api like this:
https://api.com/widgets?created_since='2017-03-24‘
Except instead of a hard coded date, the query sends the date of the last record already retrieved.
In other words, if this api call returns a billion records without filtering, how can Power Query make it manageable and efficient?
Thank you, enjoy your blog –