From past few years, excel has been getting feature-rich with various add-ins like Power Pivot, Power View, Power Query and Power BI. In this article, I am going to provide details on Power Query usage, benefits and also how it compliments to Power Pivot and Power BI.
What’s Power Query?
Power Query is an ETL (Extract, Transform, Load) feature which runs as an Excel add-in. It allows users to pull data from various sources, manipulate said data into a form that suits their needs, and load it into Excel.
How to enable it?
It is a free add-in for Excel 2010 and 2013 for Windows.
The following Office versions for Windows are supported:
- Microsoft Office 2016 – All Versions
- Microsoft Office 2013 – All Versions
- Microsoft Office 2010 Professional Plus with Software Assurance
You can go here to download and install. Microsoft Power Query for Excel is available for 32-bit (x86) and 64-bit (x64) platforms. Select the one that matches with excel version installed on your computer. Go to file –> help to check the version. Once installed, you will see Power Query under the ribbon.
Power Query is called Get & Transform in Excel 2016 and it is a built-in feature.
Power Query is not yet available for Mac versions of Excel. You can vote for these features on Excel user voice site here. It’s the topmost improvement idea under Excel for Mac forum. So hopefully, it will be available soon.
What can you do with Power Query?
Extract – You can connect to various data sources which are structured or non-structured. While you can connect to various online services like SharePoint list, I recommend using a data mart approach to ensure better performance. More on this in my next article.
Transform – GUI provides various options to transform your data. As you click to shape or transform the query, it applies steps that can later be edited.
Load – You can insert data from the query to the worksheet. You can also choose to load data to Excel data model. It is quite a handful if you want to use data to create Power Pivot tables, charts etc. An Excel Data Model is a relational data source composed of multiple tables inside an Excel workbook. Within Excel, a Data Model is used transparently, providing tabular data used in Pivot Tables, Pivot Charts, and Power View reports.
Share Queries – This requires Azure Data Catalog service. Free version supports unlimited users and up to 5,000 data catalog objects. I could not figure out how this is different from Data Catalog service provided under Power BI offering. Is Azure Data Catalog as an evolution of the Data Catalog in Power BI? Let me know in comments if you have any additional details.
Power Query, Power Pivot, Power View and Power BI
Now that we know what is Power Query, let’s see what how it compliments to Power Pivot, Power View and Power BI.
SQL Server Power Pivot for Excel is an add-in to Excel 2010 but built-in feature for higher excel versions. It can be used to create data models and subsequently used to create tables and charts.
Power View is an interactive visualization tool which provides users with a drag-and-drop interface allowing them to build quick and easy visualizations of the data in their Excel workbooks (using the Power Pivot data model).
Power BI is a suite of business analytics tools that deliver insights throughout your organization. It is offered as SaaS. It also has standalone desktop application. Power BI ties together Power Query, Power Pivot, and Power View in a standalone application.
Power BI is the service offering where Power Query, Power Pivot and Power View features are bundled and excel constraint is removed. Power Query is used to get data, Power Pivot and Power View provide visualizations while Power BI brings everything together.
Ready to transform the way you access, shape and visualize your data? By the Power of Grayskull, you have the Power!