Power Query: A Refreshing Approach to ETL (get it?)

Welcome our new Power Query blog. Today we introduce Power Query.

Regular users of Excel are well aware of the possibilities to manipulate and present data that has come from a variety of sources. Getting that data ready for each solution can be time consuming – complex solutions can require VBA or SQL expertise, and simple solutions often involve repetitive tasks before the data is ready to be uploaded to PivotTables.

Enter Power Query, which has been designed for Excel users to enable the rapid uploading and cleaning of data without needing to turn (in)to VBA and SQL experts.

Power Query is a free add-in ETL (extract, transform and load) tool for users of Excel 2010 (Professional Plus with Software Assurance version only) and 2013, where it has its own tab on the Ribbon, and is now fully integrated into Excel 2016, where it can be found on the data tab under ‘Get & Transform’.

As shown below in Excel 2013, Power Query is able to upload from many sources:

Having extracted the data from the appropriate source (more on the details of this process in later blogs), the transformation can take place – cleaning away any unrequired detail, merging where appropriate (all without the erstwhile VLOOKUP), and enhancing by calculating and adding new columns. There is even a button to unpivot data!

Whilst the majority of transformations can be done with no formulas or coding, each step is stored in M, the language behind Power Query. For the more ambitious user, M can be used to refine and build on what Power Query creates automatically.

The final step for the ETL tool is to load - to excel tables, Power Pivot, Power BI, or just store a query for use by other queries; a building block that can be re-used.

Since everything is recorded in M, then to reload to the same destination – simply refresh. 

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!