What is Power Query?

It’s a bit crazy that at this point I haven’t posted anything clearly defining what Power Query is and where it fits in the whole self-service BI experience with Power BI.

So it’s time to actually sum up some points and post them here so we can clear that doubt about what really is Power Query and why you SHOULD care about it.

What exactly is Power Query?

We can start by saying that:

• it’s a free add-in for Microsoft Excel 2010 & 2013
• Created by Microsoft
• it gets monthly updates
• its part of the Power BI add-ins family

Sure – that gives us an idea of what it is. It’s an add-in for Excel but…what does it do? what is it for? Here’s an step by step of what it does:

1. It extracts the data – from virtually any data source like a relational database, a flat file, json doc, xml, Azure, Hadoop and much more.
2. it transforms that data – and by transforming we mean A LOT of things. From merging, combining, cleaning, adding or simply change it somehow and enrich it for later use.
3. it loads that transformation into Excel – so we can analyze it with what we already know and love. Excel (or even Power Pivot for Excel!)

Another point to take in consideration is that Power Query uses a distinctive UI in order to work on a per-step basis. This means that you create transformation steps. Kind of like how you first add the data (step 1), then remove some columns (step 2) and finally renamed some other columns (step 3). Also, all of these steps are translated into its own code or language called the M Language which is not similar to DAX or Excel but pretty easy to understand once you understand the basics. (don’t be afraid of it)

(A screenshot of the Power Query UI and how steps are created and also how the M code looks like on the right)

Why Power Query and not other tools or codes?

A few reasons why:

• Ease of use – it feels intuitive! Microsoft has done an amazing job at making it super easy to use
• Practical use – of course you wouldn’t be using this if it wasn’t practical or helpful to you. Give it a try!
• Backwards compatibility! – don’t you hate when things don’t work on previous versions? well, this works on Excel 2010, 2013 and soon in Excel 2016! and you can use the same M code in all of them.
• Custom code capability – you can always create your own M code instead of going through the UI, but 90% of the time you won’t even have to bother with this since it’s all in the UI
• Easy to learn language – the M language might look tough at first, but it’s just like a person that gets misunderstood because it’s the new person on the block. Once you get to know him/her you’ll find out that it’s a pretty chill person =)
• Scalability – we’re in the year 2015 and all apps NEED to have a web story. You can rest assure that Power Query has a clear story that is getting better every month

Putting things into perspective

if you tried running a VBA code in the cloud or anywhere near Power BI or SharePoint you’ll get nowhere cause VBA is not really supported.

On the contraire, the M language and Power Query is fully supported on Power BI and they are actively releasing new updates on a monthly basis either fixing bugs or mainly adding new features and data sources.

Take a look at this screenshot to see some of the most recent added data sources:

(YES! SAP and SalesForce are listed as supported data sources for Power Query as many other data sources!)

Examples of Power Query based solutions

Let me give you some examples of a few things that you can accomplish with Power Query.

Power Query for Excel: Combine multiple files of same or different file types

Transforming Data with Power Query (Chandoo’s dataset)

and keep an eye on the Power Query category of this blog as we’ll be adding more tips and tricks using Power Query. You can also watch some of our youtube videos that show you the capabilities of Power Query.

Where does Power Query fit in the Power BI world?

Power Query has its own story on the web with the integration that exists with Power BI for Office 365 in order to:

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

Overall, what you might have already figured out is that 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,

The most interesting factor of Power Query and Power BI is that you can actually run the exact transformation that you created in Excel with Power Query to automatically happen in the cloud just by scheduling a refresh or sharing a query so it can be consumed on demand.