Power Query in Excel

In this fundamental guide, we’ll discuss the importance of Power Query in Excel. Whether you’re a beginner or looking to refresh your skills, this blog post promises to teach you practical knowledge and techniques. By the end of this journey, you’ll not only master the core concepts but also be able to apply Excel Power Query effectively in your Excel projects.

Table of Contents

• What is Excel Power Query?
• The Four Phases of Power Query
• Setting Up Power Query in Excel
• Different Ways to Import Data with Excel Power Query
• The Power Query Editor Overview
• Combining Power Queries
• Basic Text Transformations You Can Perform Using Excel Power Query
• Power Query Tabs and Options
• Working with Queries in Excel
• Conclusion
• FAQs related to Excel Power Query

Wanting to learn Excel from basic to expert level? Check out Excel course provided by Intellipaat!

What is Excel Power Query?

Power Query in Excel is a powerful data connection technology that allows you to discover, connect, combine, and refine data across various sources. It’s like a magic wand for anyone who works with data in Excel, making it simpler to transform and manipulate large datasets. Fundamentally, Power Query acts as a bridge, helping you to bring data from different places (Local Files, Web Sources, Cloud Storage, or Databases) into Excel for further analysis and processing.

Intellipaat provides a Power BI Course. Enroll now to get a professional certificate!

The Four Phases of Power Query

To really get what Power Query is all about, think of it as a four-step journey:

Connect (Data Discovery)

This is your starting point. Here, you’ll connect to different data sources. It could be anything from a simple Excel file to a more complex database.

Transform (Data Transformation)

Now that you have your data, it’s time to tidy it up. Power Query lets you filter, sort, and clean your data so it’s just the way you need it.

Combine (Data Integration)

This is where things get exciting. You can take data from various sources and combine it all together. It’s like making a big, comprehensive data puzzle where every piece fits perfectly.

Load (Data Loading)

Finally, you take your neatly organized data and put it where you need to load it, like an Excel database or an Excel sheet.

Capabilities: What Can Power Query Do?

Excel Power Query is incredibly versatile. Here’s what it can do for you:

• Data Cleansing: Remove duplicates, replace missing values, and correct data inconsistencies.
• Data Shaping: Transform data formats, merge columns, and create custom views.
• Automated Workflows: Set up processes that automatically refresh data, saving you time and effort.
• Advanced Analytics: Prepare data for complex analytical tasks, making it easier to gain insights.

Power Query is not just about making tasks easier – it’s about possibilities in data analysis and reporting in Excel. It’s perfect for beginners who are just getting started with data management and analytics.

Setting Up Power Query in Excel

Getting started with Power Query in Excel is a straightforward process, but it’s important to know where to find it and how to access its features. Whether you’re new to Excel or a seasoned user, setting up Power Query is a key step in using its powerful data-handling capabilities.

Where to Find Power Query in Excel

Power Query is integrated into Excel, but its location can vary slightly depending on the version of Excel you are using. Here’s how to locate it:

For Excel 2016 and Newer Versions:

• If you’re using Excel 2016 or a later version, Power Query is already built into the application. It’s integrated as part of the ‘Get & Transform Data’ group within the ‘Data’ tab on the Excel ribbon.

For Excel 2013 and Excel 2010:

• In these older versions, Power Query is not built-in and requires a separate add-in.
• To install the Power Query add-in, visit the Microsoft Office website and search for ‘Power Query for Excel add-in’.
• Download and install the add-in. After installation, restart Excel.

Understand the Count Function in Excel and how it is used to make data analysis easier

Different Ways to Import Data with Excel Power Query

One of the most powerful features of Power Query Excel is its ability to import data from a wide range of sources. Whether you’re dealing with local files, databases, or web data, Power Query simplifies the process, making it accessible even for beginners. Here’s a look at the various ways you can pull data into Excel using Power Query:

• From Files: This includes grabbing data from Excel files, CSV (Comma Separated Values) files, XML files, text files, and even JSON files. Whether these files are on your computer or on a shared network, Power Query can handle them.
• From Databases: If you’re working with databases like Excel Server, MyExcel, Oracle, or Access, Power Query lets you connect directly to these and import the data you need.
• From Online Services: For those who use online platforms like SharePoint or Salesforce for their business, Power Query can directly import data from these services too.
• From the Web: Power Query can also fetch data from web pages. This is super handy if you need to import data from tables or pages available online.

Importing Data from a Text File

To import a text file into Excel using Power Query, here’s what you need to do:

1. Go to the ‘Data‘ tab and choose ‘Text/CSV File‘.
2. After selecting the “Text/CSV file” option, an ‘Import data‘ dialog box will pop up.
3. Find and select the text file you want to use, then click ‘Import‘.
4. A new dialog box will appear, giving you a preview of the data in the file.
5. Finally, just click on ‘Load‘, and the data will be imported into Excel.

Getting Data from a CSV File

To import data from a CSV file into Excel using Power Query, you can follow these simple steps:

1. Start by clicking on the ‘Data‘ tab, then select ‘Text/CSV File‘.
2. After choosing the “Text/CSV file” option, an “Import data” dialog box will appear.
3. Now, pick the CSV file you want to use and click ‘Import‘.
4. A new dialog box will show up, displaying a preview of the data in the file.
5. Finally, just hit ‘Load‘ to bring the data into Excel.

Importing a Single Data Source from an Excel Workbook

To import a single data source from an Excel workbook, you can follow these straightforward steps:

1. Click on the ‘Data‘ tab, then choose the ‘Get Data‘ command. This action will display a drop-down menu with various data import options.
2. To import data from an Excel workbook, go to ‘From File‘ in the drop-down menu, and then select ‘From Workbook‘.
3. Excel will then present a dialog box to help you find and select the workbook you need.
4. After locating the workbook, click on it and then choose ‘Open’.
5. This action will bring up the navigation dialog box, which lists different data sources available in the workbook.
6. Now, you can pick the specific data you wish to work with.
7. To complete the process, simply click on ‘Load’, and the data will be imported into your Excel sheet.

Check out Intellipaat’s guide on MS Excel Interview Questions to crack good interviews for data analysis jobs.

The Power Query Editor Overview

The Query Editor in Power Query is a powerful tool where the magic of data transformation happens. It’s an environment separate from the main Excel interface, designed specifically for editing and refining data. When you load data using Power Query, it opens in the Query Editor. Here, you can perform a variety of actions like filtering, sorting, and merging data, as well as more complex transformations.

Understanding the Query List and Data Preview

1. Query List

A query list in a database is like a collection of data-handling shortcuts. Think of it as a recipe book where each recipe is a set of instructions (or queries) for managing your data. These can include tasks like collecting, sorting, or modifying data.

This setup is really useful for saving time and effort, especially if you often need to do complex things with your data. It keeps everything organized and at your fingertips.  This makes your work with data more efficient, consistent, and easy to repeat whenever needed.

2. Data Preview: 

Data Preview in Power Query Excel is like a quick glimpse at your data before you start processing it. When you load data into Power Query Excel, it shows you a sample of this data. This preview displays the first few rows and column headers, giving you an initial look at the data’s structure and quality. It’s a helpful step to check if the data is as expected and whether it needs any cleaning or adjustments before you use it for deeper analysis or reporting.

Struggling to find the right AI tools for data analysis? Browse this blog on Excel AI Tools.

Navigating the Applied Steps and Formula Bar

1. Applied Steps

Applied Steps in power query for excel are like a step-by-step record of the changes you make to your data. Each action you take, like sorting or renaming columns, is tracked as a separate step. In the Power Query Editor, you’ll find an ‘Applied Steps‘ section listing these actions in the order you performed them. This feature is a handy way to review, modify, or backtrack your steps, ensuring you have a clear and adjustable record of your data transformation process.

2. Formula Bar: 

The Power Formula Bar in Excel Power Query is like a control panel where you can see and edit the code, written in code M language, for each transformation you apply to your data. It’s a peek behind the curtain, showing how Power Query processes your actions like sorting or filtering. While it’s more advanced than using the standard interface, the Power Formula Bar in Excel gives you extra precision and control for customizing data changes, making it a powerful tool for those who want to fine-tune their data manipulation.

The File Tab and Data Loading Options

The File tab in Power Query is like a control center for handling your queries and connections within your Excel workbook. Think of it as a toolbox where you can:

1. Create New Queries: Start new projects or data explorations right from here.
2. Import Data: Bring in data from outside sources, like other files or databases.
3. Export and Share Queries: If you’ve made a query that could be useful elsewhere, you can export it to use in different workbooks.
4. Load Saved Queries: If you’ve saved queries in a query repository, you can load them up from here.

Also, under this File tab, there’s a special section called ‘Options‘. This is where you can tweak how Power Query works for you. You can set privacy levels to protect your data, adjust global settings for all your queries, and even set regional preferences, which is handy if you’re working with data from different parts of the world. So, the File tab is your go-to spot for all the key settings and actions related to your Power Query files.

Learn how to use AutoSum to quickly and easily sum numbers in your Excel spreadsheets!

Combining Power Queries

Power Query has two useful features for bringing together different datasets:

1. Append: This option allows you to stack one dataset on top of another.
2. Merge: With this, you can join two datasets side by side based on common columns.

Append

In Power Query, when you use the append feature, it’s like stacking one set of data on top of another. Imagine you have two lists of information, and you want to put them together into one long list. The append operation does just that – it starts with all the rows from your first set of data and then adds all the rows from your second set right after them. To learn how to do this, just follow these steps.

Step 1:

First, let’s get your data into the Excel workbook. In this example, we’ll focus on adding data from a CSV file. Here’s how you do it:

1. Start by going to the ‘Data‘ tab in Excel.
2. Then, click on ‘Text/CSV File‘.
3. After you select this option, an ‘Import Data‘ dialog box will pop up. Here, you need to choose the CSV file you want to use and click ‘Import‘.
4. A new dialog box will show you a preview of the data in the file. Simply click on ‘Load‘, and the data will be placed into a new sheet in your workbook.
5. Repeat these steps to add more data from other CSV files to new sheets as needed.

Step 2:

Now, if you want to put together the data from different sheets, here’s what you do:

1. Go to the ‘Data‘ tab in Excel.
2. Look for a button called ‘Get Data‘ and click on it. This will show a drop-down menu.
3. In this menu, there’s an option named ‘Combine Queries‘. Click on that.
4. After clicking, you’ll see the ‘Append‘ option. This is what you use to combine your data from different sheets.

Step 3:

When you click on ‘Append‘, a window will pop up where you can choose whether you want to combine two tables or more than three. Then, just pick the sheets you want to put together. Once you’ve made your selections, click ‘OK‘ to finish the process.

Step 4:

Once you’ve appended the data, the Power Query editor will open, showing your combined data. To save these changes, just click on ‘Close and Load‘. This will update your Excel spreadsheet with the newly combined data.

Want to know How to Insert a Checkbox in Excel? Here is your step-by-step guide.

Merge

The Merge option works like the JOIN function in Excel. It’s a method for taking two existing sets of data (queries) and putting them together to make a new set of data (a new query).

Step 1:

First, let’s bring your data into the Excel workbook. In this case, we’re going to show you how to merge data from a CSV file. Here’s how to do it:

1. Click on the ‘Data‘ tab in Excel.
2. Then, choose the ‘Text/CSV File‘ option.
3. After you select this, an ‘Import Data‘ dialog box will appear.
4. Find and select the CSV file you want to use, and then click ‘Import‘.
5. A new dialog box will show a preview of the data. Click on ‘Load‘ to put this data into a new sheet in your workbook.
6. Repeat these steps to add any other datasets you need for merging into separate sheets.

Step 2:

Now, if you want to merge the data from different sheets, here’s what to do:

1. Go to the ‘Data‘ tab in Excel.
2. Look for and click on the ‘Get Data‘ option. This will show a drop-down menu.
3. In this menu, find and select ‘Combine Queries‘.
4. After that, choose the ‘Merge‘ option.

Step 3:

A window will pop up where you can pick the sheets you wish to merge. Just click on the headers of the columns you want to use to link the sheets together. After selecting these columns, simply click ‘OK‘.

Step 4:

Once that is done,  the Power Query editor will open, showing a new column at the end, which contains the results of the merge. To save the changes you’ve made, just click on “Close and Load”.

The data that we combined is now in our Excel worksheet.

Check out the Power BI Interview Questions if you are preparing for a job interview.

Basic Text Transformations You Can Perform Using Excel Power Query

In this part, we’ll explore different transformation functions that you can do easily with just a few clicks of your mouse.

1. Text Formatting Functions

In this section, we’ll go over how to change text to all uppercase or lowercase and explain how to use the Trim function.

UPPERCASE

Step 1: Start by bringing the data you need into the Power Query Editor. You do this by choosing your data source from the ‘Get & Transform Data‘ part of the ‘Data‘ tab in Excel. This action will open the Editor, where you can make changes to your data.

Step 2: Next, click on the name of the column you want to edit. Then, head over to the ‘Transform’ tab. Here, you’ll see many different choices. Look for the text formatting option and select ‘UPPERCASE‘ from the drop-down menu that appears.

Step 3: Once you choose the UPPERCASE option, all the text in your selected column will change to uppercase letters.

Learn more about Power BI from this Power BI Tutorial!

LOWERCASE

Step 1: First, get your data into the Power Query Editor. You can do this by selecting your data source from the ‘Get & Transform Data‘ area in Excel’s data tab. This action opens the Editor, where you can make changes to your data.

Step 2: Then, click on the column you want to work on and head to the ‘Transform‘ tab. You’ll see several choices there. Look for the text formatting option and select ‘LOWERCASE‘ from the menu that appears.

Step 3: Now, you can see that all the text in the column you selected has been changed to lowercase.

Feel like your Excel calculations are just a little… off? Read on to master the Round Off Formulas in Excel that elegantly edit your entries.

TRIM

Step 1: First, select the data you need into the Power Query Editor. You can do this by selecting your data source from the ‘Get & Transform Data‘ area in the Excel data tab. This will open the Power Query Editor, where you can edit your data.

Step 2: To get rid of any extra spaces in your data, click on the column you want to fix. Then, go to the ‘Transform‘ tab, where you’ll find different options. Look for the ‘Format‘ option, and from there, select ‘Trim’ from the drop-down menu.

Step 3: Once you select the Trim option, it will remove all the extra spaces in the text of your chosen column.

2. Splitting a Column Using Delimiters

Step 1: Start by loading your needed data into the Power Query Editor. Do this by selecting your data source from the ‘Get & Transform Data’ part of the Excel data tab. This opens the Editor, where you can edit your data.

Step 2: If you want to divide a column using a specific separator (delimiter), click on the ‘Transform‘ tab and then choose the ‘Split column‘ option. A menu will appear with an option to split the data ‘By Delimiter‘.

Step 3: A dialogue box will pop up where you can pick the delimiter (separator) you want to use. After selecting it, just click ‘OK‘.

Step 4: Now, we can see that the data has been divided into two separate columns based on the delimiter you chose.

3. Transpose a Data Table

Step 1: First, get your data into the Power Query Editor. You can do this by selecting your data source from the ‘Get & Transform Data‘ part of the Excel data tab. This action opens the Editor, where you can make changes to your data.

Step 2: If you need to switch rows into columns, go to the ‘Transform‘ tab. There, you’ll find an option to ‘Transpose‘ the data.

Step 3: When you click on the transpose option, it will change the rows into columns. To bring these changes into a new worksheet, just head over to the ‘Home‘ tab and click on ‘Close and Load‘.

4. Removing Duplicates Using Power Query

Step 1: Start by selecting your needed data into the Power Query Editor. You can do this by choosing the data source from the ‘Get & Transform Data‘ area in the Excel data tab. This will open the Editor, where you can work on your data.

Now, take a look at the image below, where the duplicate data is highlighted.

Step 2: Next, go to the ‘Home‘ tab and find the ‘Remove rows‘ option. Clicking on this will show a drop-down menu. From there, select the ‘Remove Duplicates‘ option.

Step 3: You’ll see that the data no longer has any duplicates. To keep this updated version without the duplicate rows, just go to the ‘Home‘ tab and click on ‘Close and Load‘.

Power Query Tabs and Options

Transform vs. Add Column Tabs

In Power Query, there are two important areas called the Transform and Add Column tabs, each serving a different purpose for working with data:

The Transform Tab: A Closer Look

The Transform tab in Power Query is like a toolbox full of ways to change and fix up your data. It lets you do basic things like changing the type of data, swapping out values, grouping data together, changing columns around, and combining different sets of data. But it’s not just for simple changes – you can also do more complex stuff like setting up rules (conditional logic), using special functions, spreading out data differently (unpivoting), and dividing columns based on certain markers (delimiters). This tab is really useful for cleaning up your data and getting it just right for more detailed analysis.

The Add Column Tab: When to Use It

The Add Column Tab in Power Query is your go-to resource when you need to increase your dataset with new columns. It’s particularly useful when you want to create columns that aren’t originally part of your data. For example, if you need to perform calculations, the Add Column Tab allows you to easily add a new column showing the results, like sums or averages, based on existing data. It’s also handy for combining information from multiple columns into a single one, such as merging first and last names. Additionally, for more customized data insights, you can use this tab to create conditional columns, which display specific information based on certain criteria in your data. 

Utilizing the View Tab

The View tab in Power Query is like the settings for how everything looks in the Power Query Editor. It lets you adjust things to make it easier to work with your data. You can zoom in or out to see your data better, turn the Formula Bar on or off, and decide whether you want to see lines between your data cells. You can also change how wide the columns are. Plus, the View tab has options to turn on helpful hints (autocomplete) when you’re writing formulas and to choose how you want to see any errors in your formulas. 

Working with Queries in Excel

The Queries & Connections Window

The Queries & Connections window in Power Query is the central hub for all your data queries and connections in Excel. It’s a place where you can see a list of all the queries you’ve made and the connections to different data sources you’ve set up. This window makes it easy to find and change your queries, refresh them to get the latest data, and tweak how you’re connected to external data like databases or websites. It’s a really handy tool for keeping all your data tasks organized and under control in Excel.

Conclusion

Power Query in Excel is a fantastic tool that makes working with data much easier. We’ve looked at what Power Query is, its key stages, and the amazing things it can do. We’ve gone through how to set it up, how to bring in data from text and CSV files, and how to handle data from Excel workbooks. We also explored the Power Query Editor, where you can see your data, make changes, and keep track of those changes. Plus, we covered different tabs like Transform and Add Column, which help you tweak your data, and the View tab, which adjusts how things look. In short, Power Query is a great help in organizing and analyzing data in Excel, making these tasks simpler and more efficient, especially if you’re just starting out.

FAQs related to Excel Power Query