Power Query for Excel

If you find yourself repeating data cleansing tasks day in, day out such as:

• Deleting rows, columns, or removing blanks
• Sorting and filtering out data you don’t need
• Allocating relationships with VLOOKUPs so you can run a PivotTable
• Splitting or joining back together columns or text
• Removing spaces or certain characters from your data
• Merging or appending worksheets, text or CSV files
• Extracting data from an external database
• Updating a report to include the next month's data
• ...and all the other laborious, repetitive tasks that give you RSI

You imagine these tasks could be done in seconds - if only you knew how. Or maybe you have some macros that handle it all but keeping the VBA code up to date is constant task and no one else knows how to update it.

The Good News

If you have Excel 2010, or later, you can use Power Query to automate the tasks of:

1. Getting your data – from almost anywhere
2. Cleaning and reformatting your data – so it works in PivotTables etc.
3. Updating your reports – Power Query will update new or existing Tables, PivotTables or Power Pivot models

You see once you show Power Query where to find your data and how you want it cleaned and reformatted (which is dead easy), it will remember the steps so next time you can simply hit the Refresh button and it’ll do it automatically.

And this means you can get on with the fun part of analysing the data and impressing your boss so you can go home early.

To find out more, watch this demo

And the best part is there’s NO VBA or programming required. Power Query records your actions, so that each time you refresh the data each step is remembered.

In fact you’ll be up and running faster than it would take you to learn the equivalent formulas required to perform the same tasks - and you'll only have to do it once because every time you update the data, it's just a matter of hitting "refresh"!

Read this article on "Three ways to split data using Power Query"