What is Power Query? Why Do you need it?

What is Power Query and why do you need to use it

Among the hundreds of millions of Excel users, still very few people know the importance of using Power Query.

In this article, I shed light on the game-changer tool Power Query.

Get the Data with Power Query

Building a Solution and getting answers to your business questions starts by getting the data which might come from different sources, such as:

Multiple Excel Files
Text or CSV Files
Access Databases
Web Sources
PDF Files
An entire Folder of File

Power Query allows you to get the data, wherever it exists. To do that you go to the Data Tab of the ribbon and select the data source.

Analyzing data from multiple files or multiple sources all together requires bringing all this data under one single roof.

3 Vs for Data ►Variety – Volume – Velocity

The situation is even more difficult with complex data, high volume or data that changes so quickly.

Clean and Transform the Data with Power Query

Most of the time the data we get is poorly structured and badly shaped. The real truth is every one of these sources requires some kind of cleansing and transformation into a tabular format (Normalized dataset) before we can actually use Excel’s most powerful tools ( Pivot Tables, Charts, Functions…etc).

We therefore need tools to actually make sense of the data and be able to work with it.

Although in a job interview candidates need to demonstrate their reporting skills in Excel and how to use the most important functionalities, but the reality is that you spend almost 80% of your time cleansing the data to get it ready for analysis.

This is what Power Query does (ETL)

Power Query adds E(xtract), T(ransform) and L(oad) to Excel:

Extract Data from wherever it exists: Files, Databases, Websites, Folders, Exchange, Other Queries…

Transforming data means: Clean up data, Reshaping data, Removing irrelevant data, Splitting columns, merging, Appending, Unpivoting, grouping records, generate new data, create amazing calculations…etc

Loading Data : either to Excel Tables (default), Data Model (Power Pivot), Connections (Loaded when needed).

Power Query is now built inside Excel (Data Tab) and opens on top of Excel (Power Query Editor) and offers a rich array of Transformation tools in one place that not only can deal with and Fix any data problem but also record the steps of the transformation you created. If source data changes, you Refresh the result of your Query with a single click.

It’s a good investment of your time to clean your data with Power Query and build the scenario we need. When we are done, we click one button to refresh and everything is processed allover again, saving us a ton of time.