Introduction: A New Revolution

Whether we are performing basic data entry, building simple reports, or designing full-blown business intelligence solutions using VBA, SQL, and other languages, we Excel pros all deal with data to a certain extent. Our skill sets vary greatly, but the overall jobs we are usually trying to perform include:

• Transforming data to meet our needs

• Appending one data set to another

• Merging multiple data sets together

• Enriching our data for better analysis

We may get tagged with the name "data monkey," but we are actually information workers. But no matter what we call ourselves in our formal job descriptions, our role is to clean up data and turn it into information. Our jobs may not be glorious, but they are essential, and without our work done correctly, the end results of any analysis are suspect.

While Excel has an amazing toolset to help us build business intelligence out of data, converting raw data into consumable data has been a challenge for years. In fact, it's this issue that we often spend most of our time on — prepping data for analysis and getting it into a nice tabular format to expose Excel's most powerful analytical and reporting tools.

Despite the moniker "data monkey," we information workers are often more like data magicians. Our data seldom enters our world in a ready-to-consume format; it can take hours of cleaning, filtering, and reshaping to get things ready to go.

Once our data is prepared and ready, we can perform a vast array of powerful analytical processes with ease. Tools including conditional formatting, filters, pivot tables, charts, slicers, and more let us work magic and impress our audience.

But getting the data prepped and ready is the hard part. We're served dirty data, held in collections of text and Excel files (maybe a database, if we're very lucky), and we somehow have to clean it up and get it ready to use. Our end goal is simple: Get the data into an Excel table as quickly as possible, while making sure it is scoped to our needs and accurate. And every solution needs a different combination of data coming from different sources ... which takes magic.

The Benefits and Dangers of Black Magic

The true wizards of Excel use many different techniques to make their magic happen — sometimes on their own and sometimes in combination. These types of magic include:

• Excel formulas — These are some of the first techniques that the magician will often reach to, leveraging their knowledge of formulas using functions such as VLOOKUP(), INDEX(), MATCH(), OFFSET(), LEFT(), LEN(), TRIM(), CLEAN(), and many more. While formulas tend to be used by most Excel users, the complexity of these formulas varies by the user's experience and comfort.

• Visual Basic for Applications (VBA) — This powerful language can help you create powerful and dynamic transformations for your data. VBA techniques tend to be used by advanced users due to the discipline required to truly master them.

• SQL statements — SQL is another powerful language for manipulating data, and it can be extremely useful for selecting, sorting, grouping, and transforming data. The reality, however, is that this language is also typically only used by advanced users, and even many Excel pros don't know where to get started with it. This language is often thought of as being the sole domain of database professionals, although every Excel pro should invest some time in learning it.

All these tools have something in common: For many years, they were essentially the only tools available for cleaning and transforming data into something useful. Despite their usefulness, many of these tools also have two serious weaknesses: They require time to build a solution and time to master the techniques. While it's true that truly savvy magicians can use these tools to build solutions to automate and import raw data in a clean format, this takes years of learning advanced languages as well as a significant amount of time scoping, developing, testing, and maintaining the solutions. Depending on the complexity of the solutions built, fixing the solutions for a minor change in the import format or extending them to embrace another source could be horrendous.

One hidden danger of having a true wizard in a company is that the person may build an incredible solution that works until long after he or she has left the company. At some point, though, others at the company realize that they don't understand the solution and don't have anyone to fix it when it eventually breaks.

On the flip side, many people tasked with this data cleanup didn't have time or opportunity to learn these advanced magic techniques. And while we could say that maybe they're better off never having a massive system collapse without anyone to fix it, instead they waste hours, days, weeks, months, and years of labor time and money performing repetitive data cleanup and imports on a regular basis.

Take a moment and think about how many hours are consumed on a monthly basis in your company simply performing repetitive data import and cleanup tasks in Excel. Multiply those hours by the average wage rate in your company ... and by the number of companies in your industry worldwide and ... you get the idea. The cost of productivity in this area is staggering.

Enter a product that tackles all these problems — one that is easy to learn and that others can pick up and understand with limited instruction. It's a product that lets you automate the import and cleanup of data, so you can focus on turning that data into information, adding true value to your company. That product is called Power Query.

The Future Transforms

Power Query solves the problems related to the toolsets just described. It is very easy to learn and has one of the most intuitive user interfaces we've ever worked with. It's...