Power Query in Excel

Power Query in Excel is an add-on tool specializing in cleanly presenting your data. Although it might sound like something that Excel would do, it is certainly different than that.

With Power Query, you can sit back and relax while the tool does its job. When you feed it data, the Power Query tool creates a smart filter for all your items. The result? You see all your data but in a more presentable way.

Know what you can do with this tool. Here’s more on Power Query and its quite powerful ways!

What is Power Query in Excel?

Power Query in Excel is another term for Get & Transform, a feature made to overtly manipulate data within a fraction of a second. How does it achieve this?

Power queries usually work in four different ways—all part of the same goal—data acquisition, data cleaning, data combining, and data loading. The end product?

Power Query lets you quickly access data and subsets of data. This tool does not discriminate, so your skill level does not matter.

But let’s face it— you can’t use Excel efficiently without using all the shortcuts and hacks provided. That’s exactly where Power Query comes in.

This is also why you have an option to choose. This add-on brings a fresh breeze of air into your already cluttering sheet full of data.

Here’s why Power Query works out: It processes a vast dataset from websites, databases, or wherever you need to source the information.

Want to know what’s more interesting? Power Query is user-friendly, which is, perhaps, its best feature so far. For Excel users, there’s an option to drag and drop and clear menus instantly. Whether you're an expert or just starting out, this add-on tool has got your back.

Last but not least, this tool even lets you write custom functions in your own time, giving you more control over your data output.

Beyond everything, the main function of this tool is to present valuable insights in the form of data so you can make better decisions.

The features of Power Query in Excel

As someone who spends a lot of time in Excel, I know how messy data can be a pileup sometimes. Here's why Power Query has become my go-to tool for data manipulation:

1. No more data silos: Gone are the days of struggling with different data sources. Power Query connects seamlessly to databases, text files, web pages – you name it! I can import everything into a single, unified workspace.
2. Data cleaning: Power Query tackles messy data like duplicate entries, inconsistent formatting, or converting text to numbers for calculations in seconds.
3. Transformation: With the Power Query tool, I can filter down to specific data subsets, merge information from various sources, and even pivot and unpivot layouts for easy visualization.
4. Custom column: Power Query isn't limited to basic cleaning. You can create new custom columns using formulas, calculations, or even text manipulation functions.
5. M Language for Power users: Power Query offers the M Language for those comfortable with code. This feature allows you to write custom functions to automate intricate tasks, saving even more time.
6. Automated refresh: Power Query allows you to schedule automatic refreshes, ensuring your reports and visualizations always reflect the most up-to-date information.
7. User-friendly interface: Power Query is surprisingly easy to use. The drag-and-drop interface with clear menus makes it accessible to everyone. Use the visual tools for basic tasks, and for more advanced manipulations, use the M Language for granular control.

The four key aspects of Power Query

When working with Excel’s Power Query tool, remember the "connect, transform, combine, and load" strategy.

Here's a breakdown of each stage, along with the commands you can use (though the process can often be done visually as well):

Source - Enterprise DNA Blog

1. Connect

Let’s say you’re working on a sales report in a text file and customer information in a separate Excel workbook. Power Query helps you connect to these sources:

Connect to Text/CSV file: Data tab > Get Data > From Text/CSV > Browse to your file and click Import.

Connect to Excel workbook: Data tab > Get Data > From Workbook > Browse to the workbook and select the specific sheet containing your data.

2. Transform

Here are some commands for common transformations:

Remove duplicates: Select your table > Home tab > Remove Duplicates (choose the column(s) to identify duplicates).

Standardize formatting: Select a column > Transform tab > Data Type (choose the appropriate data type, e.g., Text, Date).

Filtered data: Select your table > Home tab > Filter Rows (Define your filtering criteria).

3. Combine

If you have data from both the text file and Excel workbook, you can combine them:

Merge Queries: In the Home tab, click Merge Queries > Merge Tables (Choose the tables to merge, define the join key columns).

4. Load

Once your data is ready, click Close & Load (you can choose to load it as a table or pivot table).

How to use Power Query in Excel

You will find that Power Query is installed by default as an add-on in all Excel versions from 2016 onwards. When you end up finding it, here are a couple of ways how to open Power Query in Excel.

Source - YouTube

Method 1

Step 1. Open your Excel worksheet.

Step 2. Search for the Data tab on the ribbon. Should you notice the Data tab with extended options like Get Data and Transform Data, your Power Query is already activated.

Method 2

Step 1. Open the Excel worksheet.

Step 2. Go for the File tab and select Options.

Step 3. Click on Add-Ins as it appears in the Options window on the left sidebar.

Step 4. Click on COM Add-ins and then on Go in the Manage menu.

Step 5. A list of add-ins will appear. Look for Microsoft Power Query for Excel.

Step 6. If the checkbox next to Power Query is not selected, check it to enable it.

Step 7. Click OK.

Step 8. Restart your Excel application and find the now-activated tool in the Data tab.

A brief on M Language for Power users

While the visual interface is user-friendly, a Power Query download offers the M Language for advanced transformations. Here's an example for removing duplicates using M:

let

Source = Excel.CurrentWorkbook(),

#"YourTableName" = Source{[SheetName="YourSheetName"]}[#"YourDataTable"],

UniqueTable = Table.Distinct(#"YourTableName")

in

UniqueTable

Example of Power Query in Excel

Here's an example of how I use Power Query in Excel:

Scenario: You have a sales report to publish. There are thousands of rows of data in the text file. The file seems messy as there are heaps of data, some duplicates and others formatted differently scattered throughout.

Step 1. Connect and tame the text file

Take it from the Data tab itself and choose Get Data > From Text/CSV. Navigate to the text file and click Import. Power Query displays a preview of the data.

Step 2. Transform the data

You can also call this process data wrangling. If you see duplicate entries, you select the table and head to the Home tab. With a click of the Remove Duplicates button, those duplicates vanish in no time.

Step 3. Fix incoherent data

To fix inconsistent date formats, select the date column, go to the Transform tab, and choose the appropriate Data Type. Power Query instantly formats all the dates consistently.

Step 4. Load and analyze

Once you’re satisfied with the cleaned data, it's time to bring it back into Excel. Click Close & Load in the Power Query Editor. Power Query imports the transformed data as a new, clean table in my worksheet.

Why you should be using Power Query

If you’re interested in data analysis, Excel’s Power Query would be your best match. Apart from several tools that you can use to ease your job, you will find that this tool carries some weight to it. Let me explain how.

Data analysis

In data analytics, this function will greatly improve your ability to work and manipulate data. With Excel’s Power Query tool, you don’t have to manually handle the updates anymore. Just automate inputs, and you’re good to go.

Business intelligence

In terms of business intelligence, transforming data is a vital step before moving into another important facet. Power Query is a good place to start if you’re interested in regular insights.

In general, here’s a list of options for which you can use Excel Power Query:

• Frequently handle a large amount of data, better termed complex datasets
• Automate refresh for data queries
• Combine data from multiple sources
• Spend a huge load of time removing duplicates or formatting cells

In short, if you want to work effectively without creating a pile of work, this is the tool for your needs.

The Bottom Line

We have established how Power Query can shine as your go-to tool for Excel data. From managing duplicates to filtering and merging data, this Power tool can be of immense help.

If you are looking for industry-standard Excel courses, upGrad can be the best place to get your certification and hands-on experience from leading faculty.

Check out the website and get your resume to shine! Enroll in your desired course today!

Frequently Asked Questions

1. How do I use Power Query in Excel?

Using the Power Query Editor in Excel is as easy as opening a jar of olives. Go to the Data tab>Get Data. Choose your data source i.e. File or Text/CSV. Following this, clean or reshape your data using the ribbon or formula bar. Once your data is ready, you can close and load it into your Excel sheet.

2. What is the function of Excel Power Query?

Excel Power Query can have two functions —the built-in Power Query formula language functions and custom functions.

3. What is the difference between Excel and Power Query?

Excel and Power Query are related in terms of data analysis. While Excel provides a platform for conducting data analysis, Power Query is a data manipulation tool within Excel.

4. What is a query function in Excel?

The Excel query function offers a way for users to write queries using the Power Query Formula language, which provides a powerful way to filter, sort, and transform your data.

5. What are the basics of Power Query?

You can summarize the basics of Excel Power Query in four ways —connecting to your data source, transforming data, combining data, and loading data.

6. How do you write a Power Query?

To add a Power Query in Excel, you can use the Power Query Formula Language (M). To filter data, enter the code - Table.SelectRows(#"YourTableName", [column1] > 10). Similarly, to remove duplicates, use this code - Table.Distinct(#"YourTableName").