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.
What is Excel Power Query?
Power Query for Excel is a must-have for anybody working with data, making it easy to connect, transform, and integrate data across various sources. Whether the data resides on local files, web-based, cloud-stored, or database-based, Power Query can connect, combine, and refine your data for deep analysis with minimal effort.
Let’s say Power Query serves as a channel to various data sources, connecting them to Excel for easy manipulation of large datasets. It can also automate such things as cleaning and reshaping the data, thus saving a great deal of time and improving data accuracy. Power Query allows you to bring in unrelated datasets into Excel without effort, transform them according to specifications, and analyze them in depth without difficulty.
Power Query, post-implementation, gives an extra burst for what you had coined as Excel. It turns a mere tool into a complete data analysis catalyst. Be it an analysis of financial statements or customer data, or monitoring business data, on each occasion Power Query comes in to ease your task and maximize productivity.
The Four Phases of Power Query
Power Query has become fundamental for data analysts, BI(Business Intelligence) professionals, and Excel users to simplify data preparation and transformation. Power Query operates in a four-step process: connection, transformation, combination, and loading to eliminate any agonizing experience and offer seamless efficiency to an analyst in analyzing data.
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.
For example, it would require a tiresome manual copy-paste activity if you wanted sales data from multiple Excel sheets. Thanks to Power Query, the different Excel files can be imported in one go and refreshed whenever needed.
Steps required to use the Connect feature in Excel:
- Open the program MS Excel and on the ribbon go to the “Data” tab.
- Click “From Other Sources” → Select your data source (Excel Workbook, SQL Server, Web, Text/CSV, etc.).
- If importing from Excel, select “From Microsoft Query”. Once the Microsoft Query editor opens, select “Excel Files” and click “OK”.
- Now you will be asked to select a workbook. Select the one you want to connect and click OK.
- In this case, we have selected the “Example Workbok”
Map out your data and perform data cleansing now. The best tool here is the Power Query because it allows for filtering, sorting, and transformation of raw datasets before loading them into Excel or Power BI.
For example, some of the features of Power Query are useful for standardization and cleaning of data in instances where there are inconsistent date formats or unintended blank rows in your dataset.
Steps required to use the transform feature in Excel:
- After loading the data into Power Query Editor, use some manipulation options to clean the data:
- Remove Columns – To remove a column that you think is unnecessary, right-click on the column and select Remove.
- Filter Data – Click the down arrow in the column from which you want to uncheck values.
- Change the Data Type – Click on a column, go to Transform, then select Data Type accordingly (i.e., Text, Number, Date).
- Remove Duplicates – Choose a column from the options on top, then click Remove Duplicates in the Home tab.
- Click on Close & Load to apply the transformations.
Loaded Power Query with raw dataset
After removing the column “Employee ID”.
After renaming “Salary” to “Annual Salary”.
After changing the “Salary” Data Type to Whole Number.
After filtering “Annual Salaries” below 50,000.
Final cleaned dataset on Excel after clicking “Close & Load”.
Combine (Data Integration)
The Combine function, a merging operation, is used to link data from various sources and combine them into one dataset. In Append queries, data from multiple tables can be stacked, while Merge queries join datasets on common fields (Employee ID, for example). This becomes necessary for the effective consolidation and analysis of enormous datasets.
Scenario: How to Merge Two Tables in Excel Using Power Query
Suppose you have two Excel tables, where Table 1: Employee Details (Employee_Info_Table) and Table 2: Employee Salaries (Salary_Info_Table)
Step 1:
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.
Steps to Load Data into Power Query
- Click “From Table” which is under the “Get and Transform Data” ribbon.
- Select Employee_Info_Table and click OK to load it into the Power Query Editor.
- Repeat the same steps for Salary_Info_Table.
- Both tables are now loaded into the Power Query Editor.
Step 2:
Merge Queries (Combining based on Employee ID)
- Launch the Power Query Editor.
- After selecting the “Home” tab, click on “Merge Queries” → Then select “Merge Queries as New” (this option is chosen to preserve the original data structure).
- Choose Employee_Info_Table from the first dropdown menu.
- From the second dropdown, select Salary_Info_Table.
- For both tables, click on the “Employee ID” column to set up the interrelation.
- Select “Left Outer Join” (this helps to pull through all of the employees from the first table, even if they do not have salary information).
- Click “OK.”
- Click “Close & Load” to send the merged tables back to the main Excel workbook.
Scenario: How to Append Queries by Stacking Two Tables Together?
In Power Query, the Append Queries option enables the combining of two or more tables into one. This is helpful when you want to merge tables that have the same structures, like employee tables from different sections or offices.
The two tables hold employee lists from Branch A and Branch B.
Here are the steps to append both tables into a single dataset.
Step 1: Load Tables Into Power Query
- Name the first table BranchA_Table.
- Name the second table BranchB_Table.
- Select BranchA_Table and click OK to open the Power Query Editor.
- Repeat the same for BranchB_Table to make both tables appear in Power Query.
Step 2: Append Queries
- Open Power Query Editor and choose “Append Queries”.
- Select the first dataset as the main table.
- Select the second dataset to append.
- Click on “OK” – Both the rows of the two tables are merged.
Step 3: Load The Appended Data into Excel
- Your master dataset will appear on your Excel Workbook.
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.
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:
- Go to the ‘Data‘ tab and choose ‘Text/CSV File‘.
- After selecting the “Text/CSV file” option, an ‘Import data‘ dialog box will pop up.
- Find and select the text file you want to use, then click ‘Import‘.
- A new dialog box will appear, giving you a preview of the data in the file.
- 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:
- Start by clicking on the ‘Data‘ tab, then select ‘Text/CSV File‘.
- After choosing the “Text/CSV file” option, an “Import data” dialog box will appear.
- Now, pick the CSV file you want to use and click ‘Import‘.
- A new dialog box will show up, displaying a preview of the data in the file.
- 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:
- Click on the ‘Data‘ tab, then choose the ‘Get Data‘ command. This action will display a drop-down menu with various data import options.
- To import data from an Excel workbook, go to ‘From File‘ in the drop-down menu, and then select ‘From Workbook‘.
- Excel will then present a dialog box to help you find and select the workbook you need.
- After locating the workbook, click on it and then choose ‘Open’.
- This action will bring up the navigation dialog box, which lists different data sources available in the workbook.
- Now, you can pick the specific data you wish to work with.
- To complete the process, simply click on ‘Load’, and the data will be imported into your Excel sheet.
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.
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.
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:
- Create New Queries: Start new projects or data explorations right from here.
- Import Data: Bring in data from outside sources, like other files or databases.
- Export and Share Queries: If you’ve made a query that could be useful elsewhere, you can export it to use in different workbooks.
- 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.
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.
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.
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
In Power Query, there are two important areas called the Transform and Add Column tabs, each serving a different purpose for working with data:
| Feature | Transform Tab | Add Column Tab |
| Primary Use | Modifying existing data. | Adding new columns based on calculations or existing data. |
| Key Actions | – Filtering rows– Sorting data– Removing duplicates– Splitting columns | – Creating calculated columns<– Using functions and formulas– Applying operations on data |
| Focus | Changing the way current data is displayed or organized. | Expanding the dataset with additional information or metrics derived from the current data. |
| Examples | – Changing a column’s data type– Merging two columns into one– Removing unnecessary rows | – Adding a column that calculates the sum of two other columns– Creating a column with conditional formulas |
Data Cleaning & Restructuring
The Transform Tab in Power Query is from where users can clean, format, and edit the information before loading it into the Excel sheet. This tab has various features that allow you to work with damaged datasets, clean them, and suit them for deeper analysis.
Features of Transform Tab:
- Change Data Types – Set text, number, and date formats to the correct type.
- Filter & Sort Data – Delete unwanted rows, sort values, and narrow down datasets.
- Remove Duplicates – Reduce repetitions on records within spreadsheets.
- Replace & Modify Values – Modify incorrect or empty values in surplus.
- Unpivot & Pivot Data – Alter the representation of tables for better analysis.
The Transform Tab is Crucial Since:
- Standardizes data before analysis, helping to minimize errors.
- It eliminates the need for experts in manual data cleansing and completes the task much faster.
- Prepares datasets for office automation and Excel reporting enhancements.
To improve your ability to incorporate Excel into your workflow and make informed decisions, you must first master the Transform tab.
The Add Column Tab
The Add Column Tab is essential for modifying your data so that it meets your business intelligence requirements. This includes simply improving its insight, actionability, or overall quality.
When to Use It:
- Do calculations – Insert formulas such as sum, average, or percentage changes.
- Merge or Split Columns – Merge names, extract data from text, or split values.
- Create Conditional Columns – Employ IF-THEN logic to divide data dynamically.
- Add Custom Formulas – Employ Power Query’s formula language (M Language) to leverage complex logic.
The View Tab
The View Tab in Power Query is often overlooked, despite its importance in improving how we see data, navigate to it, and troubleshoot any issues. The View Tab improves the overall workflow, allowing users to focus on data transformation without the hassle of correcting mistakes.
Using the View Tab in Power Query
- Enable Formula Bar – Changes can be viewed & edited as they are being applied.
- Activate Data Previews – Change the preview options and improve query processing.
- Display Applied Steps Panel – Tracks every change the user makes to the dataset.
- Change Column Widths – Table cell visibility is improved, making the data more readable.
- Activate Error Handling Mode – To quickly identify and resolve issues with the data transformation attributes.
With the View Tab, you can configure your Power Query workspace in a professional manner, allowing you to work more efficiently and effectively.
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
In today’s data-driven world, Excel Power Query is more than just a tool; it is a revolution for analysts, business users, and Excel experts. Whether you are working with large data sets, automating data cleansing, or combining multiple sources, Power Query makes it all simple and efficient, saving you time and effort.
With Excel Power Query expertise, you have limitless possibilities for data analysis and business intelligence. It’s time to upgrade your Excel game and let data work for you!
Looking to become a Power Query & Excel automation expert? Enroll in Intellipaat’s Free Excel Certification Course Online and acquire knowledge from industry experts!
Begin your journey now and revolutionize the way you work with data!