If you find yourself repeating data cleansing tasks day in, day out such as:
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.
If you have Excel 2010, or later, you can use Power Query to automate the tasks of:
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.
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"