...

What is Power Query? Why Do you need it?

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

Table of Contents

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.

That is why Power Query is amazing and knowledge of Power Query will be the norm for hiring new employees for positions where Excel is needed.

The OfficeInstructor center offers Corporate training in Power Query, and in a one single day you will be able to use this amazing functionality.

Share This Post
Have your say!
0 0

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Subscribe to our Newsletter

Receive 7 Instant Gifts when you Subscribe to my monthly Newsletter below.

Thanks for Downloading Your Gift

We have sent your quick reference guide for creating Dashboard.

Please check your inbox to receive Your Gift.

Happy Learning !!

Subscribe to our Newsletter

Receive 7 Instant Gifts when you Subscribe to my monthly Newsletter below.

Seraphinite AcceleratorOptimized by Seraphinite Accelerator
Turns on site high speed to be attractive for people and search engines.