Home>Blogs>Power Query>Mastering Merged Queries & PIVOT Column Techniques in Power Query
Lookup and Pivot in Power Query
Power Query

Mastering Merged Queries & PIVOT Column Techniques in Power Query

Power Query is a game-changer for those looking to improve their data analysis skills in Excel. With its variety of functionalities, this tool has particularly emphasized its importance in data transformation. This article will provide a detailed walk-through of two core techniques: Merged Query and PIVOT Column, and how you can seamlessly implement them using Microsoft Power Query for Excel.

Understanding the Basics

What is Power Query?

Power Query is an Excel and Power BI tool for data connectivity, enrichment, and transformation. Its primary role is to help users import data from a range of sources, clean, transform, and shape the data according to their needs, and then load it into Excel worksheets or Power BI models.

What is Merge Query in Power Query?

Merge Query is a feature in Power Query that allows users to combine rows from two or more tables based on one or more common columns (akin to SQL JOIN operations). This is highly beneficial when you need to fetch data from multiple tables or sources that share at least one column in common.

Merge Query in Power Query
Merge Query in Power Query

What is Pivot Column in Power Query?

The Pivot Column functionality enables users to transform data from a stacked or normalized format into a more readable, cross-tabulated format. In simpler terms, it rotates unique values from one column into multiple columns in the output, much like the pivot table feature in Excel but with more transformation capabilities.

Pivot Column in Power Query
Pivot Column in Power Query

Datasets Used in this Example

To better understand these features, we will delve into a practical example using two datasets:

Emp Master:

This dataset includes columns such as EMP ID, Emp Name, and Department. Here, the EMP ID acts as the key for our merge query.

Emp Master
Emp Master

Emp Attendance:

Comprising columns like Date, EMP ID, and Attendance, this dataset, too, uses EMP ID as its merge key.

Emp Attendance
Emp Attendance

Hands-On with Merge Query & Pivot Column

Using the given datasets, we aim to combine them using the merge query and subsequently transform the combined set using the Pivot column. Our goal is to create a “Final Attendance” table where the Date column, initially in rows, is pivoted into columns. This will give us a clearer overview of each employee’s attendance over a span of dates.

Final Attendance
Final Attendance

Visit our YouTube channel to learn step-by-step video tutorials


Watch the step-by-step video tutorial:

Advantages of Mastering Merged Query & PIVOT Column Techniques

  • Enhanced Data Visualization: Pivoting columns provides a concise and visually appealing representation, especially for date series data.
  • Time-Efficiency: With Merge Query, you can swiftly combine data from various sources, reducing the time taken for manual consolidation.
  • Accurate Analysis: Merging on common keys minimizes errors, ensuring the data’s integrity is maintained.

Best Practices for Mastering Merged Query & PIVOT Column Techniques

  • Clean Data: Ensure your datasets are clean and free from discrepancies before merging.
  • Understand Your Key: Always know which column you’re using as a key for the merge to avoid inaccurate results.
  • Limit Pivoted Columns: While pivoting, be wary of creating too many columns, which could complicate the analysis.


Mastering the Merged Query and PIVOT Column techniques in Power Query can dramatically upgrade your data analysis and visualization capabilities in Excel. By understanding and implementing these functionalities, you’re ensuring a more streamlined, accurate, and visually appealing data analysis process.

Frequently Asked Questions

Q. How do I access Power Query in Excel?

Firstly, Power Query can be accessed in Excel via the ‘Data’ tab. If you happen to be using Excel 2016 or later, then, fortunately, it’s built-in and can be conveniently found under ‘Get & Transform Data’.

Q. Is Power Query available for all Excel versions?

Well, not exactly. Initially, Power Query was introduced for Excel 2010. However, if you’re using Excel 2016 or a later version, you’ll be pleased to know it’s integrated directly.

Q. Can I use Power Query with other data sources besides Excel?

Absolutely! While many start with Excel, Power Query indeed supports a broad range of data sources. For instance, it connects with SQL Server, SharePoint, and, interestingly, even online services like Facebook.

Click here to download the practice file


Meet PK, the founder of PK-AnExcelExpert.com! With over 15 years of experience in Data Visualization, Excel Automation, and dashboard creation. PK is a Microsoft Certified Professional who has a passion for all things in Excel. PK loves to explore new and innovative ways to use Excel and is always eager to share his knowledge with others. With an eye for detail and a commitment to excellence, PK has become a go-to expert in the world of Excel. Whether you're looking to create stunning visualizations or streamline your workflow with automation, PK has the skills and expertise to help you succeed. Join the many satisfied clients who have benefited from PK's services and see how he can take your Excel skills to the next level!