What is Power Query?

What is Power Query?

The Best Transformation and Data Manipulation Tool for Excel

The Common Scenario of Every Excel User

Performing Magic with Your Data

From the basic Excel user who creates simple reports, or just enters data into a single spreadsheet, to the most advanced VBA developer or Excel BI specialist, we all deal with data to a certain extent. More often, we usually:

Transform the Data

Transform the Data

Transform the data somehow in order to meet our desired structure

Enrich the Data

Enrich the Data

Adding new columns, rows or performing operations for later analysis

Append or Merge Data

Append or Merge Data

We might need to combine, consolidate, append or merge data

And many, many, MANY other processes that we do with the data in order for it to be in the format or structure that we need.

We put a lot of effort into achieving our desired result. In the end, we are information workers that try to shape the data with the tools that we have.

InformationWorker

So, to be more clear about what we do as Excel users, I’ve created this diagram that clearly shows what we do:

DataMagic

Yes, we are magicians when it comes to data shaping and transforming. Data Magicians to be more precise.

Let’s Meet this Magic Tool

Let’s make Magic with our Data using Power Query (click to expand the toggles below)
The best FREE Excel add-in since Power Pivot

Power Query is a free add-in created by Microsoft for Excel 2010 (or later) and also for the Power BI Designer. It’s an intuitive tool that lets you manipulate, transform, consolidate, enrich, merge and do much more with your data.

It also acts as an ETL tool which means that it Extracts data from almost any data source, Transforms that data somehow and then Loads it somewhere – in our case that’s either Excel, Power Pivot or the Power BI Designer canvas.

It’s the new and optimized version of all that you can find in the Data tab plus much MUCH more.

This is how it looks on all the versions of Excel and the Power BI Desktop

power-query-versions

Easier to use, understand and master than other tools

Power Query has a short learning curve compared to other tools and technologies like VBA, but the tool doesn’t compromise on benefits. In fact, it’s a tool that was developed specifically to address data manipulation and data transformation and it addresses them quite well.

We are bold enough to say this: You’ll become comfortable transforming data with Power Query faster than you can become comfortable doing the same with VLOOKUP, INDEX, MATCH, OFFSET, INDIRECT and other functions. When transforming and manipulating data, you’ll also be able to develop solutions faster and return results quicker via Power Query than compared to trying to do the same with VBA.

Powerful BI & Cloud Story
excel-y-power-bi

Power Query has an entire web story with the integration that exists with Power BI for Office 365. This allows you to:

  • Share and Manage queries
  • Create roles based on who creates the queries and who gets to certify them (data stewardship)
  • Expose local or on-premises data sources to the cloud through a secure gateway (with the Data Management Gateway), which can be searched and consumed with Power Query

Overall, Power Query is a scalable solution. It can start on your desktop as part of a local solution but it can be fully deployed to a more robust and extensive solution on the web.

But the most interesting factor of Power Query and Power BI is this: You can use the exact transformation you created in Excel with Power Query and run it in the cloud just by scheduling a refresh or sharing a query so it can be consumed on demand.

All
0

Join our newsletter now and get a FREE copy of our eBook

'Magic Tricks for Data Wizards: TIps & Tricks for Power Query in Excel & Power BI'

x