Power Query is an add-in for Excel that helps us easily import data from many different sources into Excel. During that process, we have the option to create new columns, change existing ones, filter out rows, and generally shape the data before it arrives in Excel. This is different from the capabilities we have had in Power Pivot as Power Query allows us to work with data BEFORE it arrives in our data model. With Power Pivot, you must import the data before you can do anything else with it. I don’t mean this as a dig against Power Pivot. Power Pivot is an amazing tool and I absolutely love it. Power Query merely fills a gap in the user story. If you are familiar with SQL Server Integration Services, you can think of Power Query as the End User version. That is not totally fair to Power Query, but if that helps with your foundation, then go for it.

A Power Query query is a job that imports data from one or more sources, optionally modifies or shapes that data, then loads the results to Excel. A query is composed of Steps arranged in a specific order. Each action you take in Power Query will produce steps. Sometimes multiple similar actions will be combined into a single step. Occasionally, a single action will result in multiple steps.

This series will go through various capabilities of Power Query and show how to use some of them. As this is meant for beginners, I will not be covering the Power Query Formula Language (informally known as “M”). That is the language behind the scenes in Power Query. Everything you do in the tool generates M code that will be executed against your data. M is tremendously powerful and takes Power Query to exciting places. However, I think that is best left for a deeper dive. Since the vast majority of what people will need to do with Power Query is possible directly in the user interface provided, we will focus there.

Being a Decathlon, this series will have 10 installments:

Power Query Decathlon – Beginner 01: Installation and Tour

Power Query Decathlon – Beginner 02: Importing Data

Power Query Decathlon – Beginner 03: Managing Columns and Rows

Power Query Decathlon – Beginner 04: General Transformations

Power Query Decathlon – Beginner 05: Text Columns

Power Query Decathlon – Beginner 06: Number Columns

Power Query Decathlon – Beginner 07: Date Columns

Power Query Decathlon – Beginner 08: Error Handling

Power Query Decathlon – Beginner 09: Loading Results

Power Query Decathlon – Beginner 10: Query Management

By the end of this series, you should have a solid foundation with which to start working with Power Query.

Note: At the time of this writing, automating the refresh of data in Power Query is only possible if you have a Power BI subscription. As such, it is difficult to consider Power Query to be ready as an Enterprise resource. It is still a great tool for end users and for targeted solutions where total automation is not necessary. That said, it is safe to assume that Microsoft is aware of this limitation and we will likely see things improve as Power Query matures as a product.

Power Query is not bound to the development cycle of any other tool. As such, it is updated pretty frequently, sometimes monthly. This series was put together using the November 2014 version of Power Query. In other versions, screens and functionality could be different (likely better).