Power Query is a free add-in for Excel (versions 2010 and later) that adds some powerful ETL (Extract Transform & Load) functionality to Excel; and in doing so gives us the option of developing the complete end to end BI solution in Excel. In Excel 2016 Power Query is no longer an add-in, its an integral part of Excel that can be found on the Data ribbon.
We can use Power Query to connect Excel to a vast array of data sources; SQL, Oracle, CSV files, OData feeds, SharePoint lists……the list goes on! We can even use it to connect Excel to other BI technologies such as Business Objects universes.
Data loaded into Excel from these sources can either be loaded into Excel tables or directly into a Power Pivot data model. The later makes sense if you’re going to add the resulting Excel tables to a data model anyway.
We’ve successfully used Power Query in a number of client solutions and we’ve been very impressed with it, and we think you will be too.
- Instructions on how to activate Power Query for Excel versions 2013 and earlier can be found in our blog
- Power Query for Excel versions 2013 and earlier can be downloaded from here.