How many times have you ended up using loads of VLOOKUPs to show data from 2 or more different tables together? Although this works it is very memory intensive which on large spreadsheet can cause delays in updating at best or crashes at worst. Have you always wondered if there is an easier way? Well, there is – Power Query!!!
Power Queries has been available as an add-in in Excel in 2010 and 2013 but although it became a fully installed option in 2016 they are still underused with many people not realising the option even exists. They are now sometimes referred to as Get and Transform as this is where it sits on the ribbon.
What is Power Query?
Power query is an Excel feature which enables you to extract data from more than 1 table efficiently but also has loads of time saving features to help tidy up the data, format the data and perform calculations.
Where do I find Power Query?
On the Data tab, the Get and Transform section gives options about where to get the data from. Choices include Excel Workbooks, CSV/Text files, SharePoint, SQL, other databases and more. Although this is not labelled as Power Query it is still often referred to as such due to its origins and is now sometimes called Get and Transform instead. The data is not actually imported but instead a connection is made to the original data so if it updates the query only needs to be refreshed and it updates too.
When a query is created a Query tab appears at the end of the ribbon. Initially it doesn’t look like there are many choices but some of the most useful options are detailed here. Duplicate will give you an exact copy of the query with all the applied steps (more on steps later), Reference will create a reference to the original query instead as a new query. There is also the option of merge which produces a result similar to that which can be achieved using a VLOOKUP allowing for data in 2 related tables to be combined. Append allows the opportunity to add tables below each other instead, for example monthly tables can be summarised in a yearly one.
Edit is probably the most powerful option from the main screen as it opens up a whole new range of features.
The first things to be aware of is that once any changes have been made the option of close and load from the home tab must be used to apply these changes to the underlying data. Filters are automatically applied to the column headings which can be used to extract specific data in much the same way as in Microsoft Excel. Some options are available on more than one of the tabs.
This contains options such as Refresh, remove rows and columns not required, sort, change data type, split column (like text to columns in Excel) and Group by to summarise data.
Contains options such as changing case, extracting characters at the start or end of the text and changing how dates are seen. These would need functions to perform the equivalent operation when working in the main Excel application so Power Queries are perfect for this too.
This allows for calculated columns or an index to be added.
All the choices made while in the query editor produce applied steps in the query settings pane that appears on the right of the screen. These can be deleted or amended by using the cog symbol that appears. Just be careful if you remove an earlier step without removing later ones as the query could fall down depending on what is removed.
Power BI uses very similar options to transform data so if you learn how to use some of these features here you are already part wat to using a totally different product. It produces fantastic visualisations in reports so share data amongst colleagues and clients.