Introduction to Power Query

Introduction to Power Query

Hi Friends, we are starting with a new series of articles on a very important feature of MS Excel. Yes, that is Power Query. It was first introduced with Excel 2010 version and started gaining attention of users due to its capabilities and features. So, what is Power Query, let’s start from this question.

What is Power Query?

Power Query is a business intelligence tool available in Excel and Power BI that allows you to import data from many different sources and then transform and analyze the data.

Excel VBA Master Tool Kit

Through Power Query you can setup a query once and reuse it with a single click refresh button. As the businesses are growing so as the data is also increasing day by day. Power Query is a powerful feature which can import thousands and millions of records into the data model for analysis.

The user interface is also very intuitive and does not require special skill to learn it. With the easy navigation, you can quickly import your data and start analyzing in few minutes only.

So what you can do with your data through Power Query. Well the answer is Almost Everything. It allows multiple ways to import the data then transform it using cleanup, filters, sort etc. and finally Publish or export the data. 

How to use Power Query?

Using Power Query, you can import data from various source such as Excel, Text, Access, SQL, Web, Facebook, Azure and many more.

Introduction to Power Query

Once you have imported the data, the next step is to clean and transform it. You can do it through the amazing features provided by Power Query such as changing data types, removing columns rows, blanks, find and replace, text to column, split column, sum, rounding, calculations, filters, sort, transpose etc.

Power Query

If you have multiple data sets that you want to consolidate into one query, then you can use append or merger queries feature.

Introduction to Power Query

The next step is to summarize your data through Group By option.

Introduction to Power Query

The final step is to publish or export your data to Excel file. You can do this through “Close & Load” button and your data is ready to share.

Introduction to Power Query
Introduction to Power Query

There are majorly two advantages of Power Queries as mentioned below

  • No changes to your Source Data: Power Query does not make changes to your data source, it simply makes connection to your data source and reads the data. So you can rest assured that your data will not be changed by Power Query.
  • Re-usability: You can create your Power Query with one data set and run it with another data set without making much changes. This saves time in avoiding rework due to data change.

Download Practice File

You can also practice this through our practice files. Click on the below link to download the practice file.
Download Practice File

Recommended Articles

Similar Posts

How to use Goal Seek in Excel for What-If analysis
| |

How to use Goal Seek in Excel for What-If analysis

BySumit Shrimal

This tutorial shows how to use Goal Seek in Excel 365 – 2010 to get the result you want by changing an input value.
What-If Analysis is one of Excel’s most powerful but least understood features. Simply put, it lets you try out different scenarios and see possible outcomes. In other words, it helps you see how changes affect your data without actually changing the real data. In this tutorial, we’ll focus on one of Excel’s What-If Analysis tools—Goal Seek

How to color alternate rows in Excel: highlight every other row
| | |

How to color alternate rows in Excel: highlight every other row

BySumit Shrimal

This tutorial shows you how to change the row colors in Excel to automatically highlight every other row or every nth row or column in your worksheets. You will also learn how to use Excel’s banded rows and columns and find some helpful formulas to shade rows based on value changes.
Using alternating colors for rows in Excel is a common way to make data easier to read. While it’s simple to manually highlight rows in a small table, it can be very time-consuming in larger tables. A better approach is to automatically alternate the colors of rows or columns, and this article will show you how to do it quickly

How to Sort Data Using Power Query in Excel: A Step-by-Step Guide

How to Sort Data Using Power Query in Excel: A Step-by-Step Guide

Byexcelsirji

This article unveils the magic of Power Query, a built-in Excel tool that simplifies data organization. Learn how to sort by single or multiple columns, create layered sorts for complex needs, and even reverse your data order entirely. Power Query puts you in control, transforming your data into a well-structured format for effortless analysis.

Advance Filter in Excel – how to create and use
| | |

Advance Filter in Excel – how to create and use

BySumit Shrimal

This guide explains the basics of Excel’s Advanced Filter and shows you how to use it to find records that match one or more complicated conditions.
If you’ve read our previous guide, you know that Excel’s regular filter offers different options for filtering text, numbers, and dates. These options work well for many situations, but not all. When the regular filter isn’t enough, you can use the Advanced Filter to set up custom criteria that fit your exact needs.
Excel’s Advanced Filter is especially useful for finding data based on two or more complex conditions. For example, you can use it to find matches and differences between two columns, filter rows that match another list, or find exact matches with the same uppercase and lowercase letters.
Advanced Filter is available in all Excel versions from 365 to 2003. Click the links below to learn more.

One Comment

  1. Dhananjay Pinjan says:

    Very clear explanations.. Thanks.. Need more videos on PQ…

    Reply

Leave a Reply

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

Excel VBA Master Tool Kit