Now that you know the basic idea behind Power Query and Power Pivot it’s time to start downloading them! (if you have no idea what I’m talking about you may want to read my earlier posts here , , .
The thing to remember is that Power Query and Power Pivot aren’t just Excel features – they pop up in a few places and if you know how to use them in one application you can easily use them in another.
See below for some housekeeping on where to get access to Power Query and Power Pivot.
|Application||Power Query||Power Pivot|
|Excel 2010 (Windows)||Download Add-In (“Professional Plus” Excel Only)||Download Add-In
(All Excel versions)
|Excel 2013 (Windows)||Download Add-In (All Excel Versions)||Default Add-In
(Pro Excel only)
|Excel 2016 (Windows)||Included (called “Get & Transform”)||Default Add-In
(Pro Excel + Standalone Excel only)
|Power BI Desktop||Included|
|MS SQL Server||Add-In (SQL Server Data Tools or Analysis Services Project)|
The best experience for using Power Query and Power Pivot is within Power BI as it has regular monthly updates with the newest features.
Standalone Excel means the single-application purchase of Excel without the rest of the Office suite.Note: “Pro” Excel refers to Excel that comes via the Professional Plus package installation of the MS Office suite included in subscriptions of Office 365 (ProPlus, E3, E4, E5, Education E5) as well as the Professional Plus 2016 non-subscription installation.
For Excel the best option is via Office 365, with updates being delayed by 1-2 months from Power BI depending on your Office 365 channel. I’m currently on an Education E5 subscription through my university (current channel) and an E5 subscription through work (deferred channel).
In future it’s expected that Power Query will also be available in SQL Server Reporting Services (to allow bypassing of a SSAS tabular model), as well as SSIS, and perhaps even other applications such as Access and Visio.
Now that you’re all set to go see here (coming soon) to get started with building your first model, or read on here to learn about the basics of databases and tables.