Master Excel Power Query Pivot & Unpivot: Examples for 2026 Data
Are you constantly wrestling with data in Excel that's formatted for input, not analysis? Many data analysts and reporting professionals face the daily challenge of converting wide, flat tables into tall, normalized lists, or vice-versa. This struggle often limits your ability to create meaningful reports and dashboards efficiently. Thankfully, excel power query offers powerful data transformation tools: Pivot and Unpivot. This guide dives deep into practical power query pivot and unpivot examples, demonstrating how these features, enhanced by Microsoft Copilot, can revolutionize your data preparation for reporting in 2026.
Understanding how and when to use Pivot and Unpivot is fundamental for anyone serious about mastering data transformation and establishing robust excel etl with power query and copilot workflows. These functions are not just about rearranging data; they are about structuring it for optimal analysis, enabling you to extract deeper insights from your multi-source datasets.
Why Pivot and Unpivot are Crucial for Data Analysts
In the world of data analysis, data rarely arrives in the perfect shape for immediate use. Often, you receive spreadsheets where dates are columns, or categories are spread across numerous fields. This 'messy data' requires careful handling to become actionable.
Understanding Data Structures: From Wide to Tall
Imagine a dataset where each month of the year is a separate column. This 'wide' format is great for manual entry but terrible for calculating year-over-year trends or filtering by month. Conversely, a 'tall' format, where 'Month' is a single column and values are in another, is ideal for most analytical tasks. Pivot and Unpivot are your go-to tools for navigating between these formats.
Common Scenarios for Reshaping Data
You'll encounter numerous situations where reshaping data is essential:
Consolidating Survey Responses: If each question's answer is a separate column, unpivoting can transform this into a clean attribute-value pair.
Financial Reporting: Turning budget or actuals spread across quarterly columns into a single 'Value' column with a 'Quarter' attribute.
Sales Analysis: Pivoting sales data from individual transactions into a summary table showing sales by product category across different regions.
Preparing Data for BI Tools: Most business intelligence tools (like Power BI) prefer normalized, 'tall' data for better performance and easier relationship modeling.
Mastering Power Query's Pivot Feature with Examples
Pivoting is the process of transforming unique values from one column into new columns, often accompanied by an aggregation. It helps you summarize data horizontally.
Step-by-Step: Pivoting a Dataset
Load Data: Import your data into Power Query Editor (Data > Get Data > From Table/Range).
Select Pivot Column: Identify the column whose unique values you want to become new column headers (e.g., 'Month').
Apply Pivot: Go to 'Transform' tab, click 'Pivot Column'.
Choose Value Column & Aggregation: In the dialog, select the column containing the values you want to aggregate (e.g., 'Sales Amount') and the aggregation method (e.g., Sum, Average, Count).
Load to Excel: Close & Load your transformed data.
Leveraging Power Query M Language for Advanced Pivoting
While the UI is intuitive, the underlying M code offers more flexibility. After a basic pivot, you can open the 'Advanced Editor' to see the Table.Pivot function. This function allows you to specify a list of values for the pivot column, handle nulls, or even apply custom aggregation functions beyond the standard options. For instance, to pivot by a dynamic list of months, you might use Table.Distinct to derive the list first.
Worked Example 1: Pivoting Sales Data
Let's say you have sales data structured like this:
RegionMonthSales AmountNorthJan1500SouthJan2000NorthFeb1700SouthFeb2200EastJan1800
To see total sales by region for each month, you would:
Load this data into Power Query.
Select the 'Month' column.
Click 'Pivot Column' under the 'Transform' tab.
For 'Values Column', select 'Sales Amount'.
For 'Aggregate Value Function', choose 'Sum'.
The result would be:
RegionJanFebNorth15001700South20002200East1800null
This provides a cleaner, summarized view, simplifying your reporting.
Demystifying Power Query's Unpivot Feature for Clean Data
Unpivoting is often the unsung hero for how to clean messy data in excel power query. It transforms columns into rows, creating a 'tall' dataset that's far more amenable to analysis, filtering, and charting.
Step-by-Step: Unpivoting a Dataset
Load Data: Get your wide data into Power Query Editor.
Identify Attribute Columns: Select the columns you want to unpivot (i.e., the ones whose headers represent values/attributes).
Apply Unpivot: Right-click on the selected columns (or go to 'Transform' tab) and choose 'Unpivot Columns' or 'Unpivot Other Columns'.
Rename Columns (Optional): The new columns will be named 'Attribute' and 'Value'. Rename them to something more descriptive like 'Quarter' and 'Amount'.
Load to Excel: Close & Load the transformed data.
Unpivoting Other Columns vs. Unpivoting Selected Columns
Power Query offers two main unpivot options:
Unpivot Selected Columns: You explicitly choose the columns to be unpivoted. All other columns remain as they are. This is useful when you have a clear set of columns to transform.
Unpivot Other Columns: You select the columns you want to *keep* (e.g., 'Region', 'Product ID'). All *unselected* columns will then be unpivoted. This is often safer if your dataset might have new columns added in the future that you'd want to include in the unpivot.
Worked Example 2: Unpivoting Financial Reporting Data
Consider a financial report with quarterly data in separate columns:
AccountQ1 2026Q2 2026Q3 2026Q4 2026Revenue1000120011001300Expenses500600550650
To analyze this by quarter, you'd want a 'Quarter' column and an 'Amount' column:
Load this data into Power Query.
Select the 'Q1 2026', 'Q2 2026', 'Q3 2026', 'Q4 2026' columns.
Right-click and select 'Unpivot Columns'.
Rename 'Attribute' to 'Quarter' and 'Value' to 'Amount'.
The transformed data becomes:
AccountQuarterAmountRevenueQ1 20261000RevenueQ2 20261200RevenueQ3 20261100RevenueQ4 20261300ExpensesQ1 2026500ExpensesQ2 2026600ExpensesQ3 2026550ExpensesQ4 2026650
This normalized format is perfect for charting trends over quarters or comparing accounts directly.
Boosting Your Workflow with Power Query Copilot
Microsoft Copilot is rapidly changing how data analysts interact with tools like excel power query. For pivot and unpivot operations, Copilot acts as an intelligent assistant, streamlining complex transformations.
Generating M Code with Copilot
Instead of manually clicking through menus or trying to remember specific M functions, you can ask Copilot to generate the M code for you. For instance, you could prompt: "Write Power Query M code to unpivot all columns except 'Date' and 'Product ID' and rename the new columns to 'Metric' and 'Value'." Copilot can provide the exact Table.UnpivotOtherColumns syntax, saving significant time, especially when dealing with dynamic column sets or complex aggregation logic for pivoting.
Debugging and Optimizing Queries
Copilot isn't just for creation; it's also a powerful debugging tool. If you encounter an error in your M code or want to optimize a slow query, Copilot can analyze your steps in the query editor, suggest potential fixes, or even propose more efficient ways to structure your data transformation steps, including proper handling of data types and parameters.
Advanced Tips for Excel ETL with Power Query
Beyond pivot and unpivot, integrating these techniques into a broader etl (Extract, Transform, Load) strategy is key for data professionals.
Combining Data: Merge vs. Append Queries
Once your data is properly shaped, you'll often need to combine it. Power Query offers two primary ways:
Merge Queries: Used to combine data horizontally, much like a SQL JOIN, based on common columns. Essential for enriching a primary data source with lookup information.
Append Queries: Used to combine data vertically, stacking rows from multiple data sources with identical column structures. Perfect for consolidating monthly reports or different regional datasets.
Understanding these operations alongside pivot and unpivot completes your data transformation toolkit within Power Query.
Best Practices for Data Types and Parameters
Always ensure your data types are correctly set in the query editor. Incorrect data types can lead to errors during pivot/unpivot or subsequent analysis. Leveraging parameters allows you to build flexible queries where key values (like a file path or a specific report month) can be easily changed without editing the M code. For instance, you could parameterize the aggregation column in a pivot operation or the 'value' columns in an unpivot, making your queries more reusable and robust.
Mastering pivot and unpivot in excel power query, especially with the intelligent assistance of Microsoft Copilot, equips you with critical skills for any data analyst or reporting professional. These data transformation techniques are indispensable for turning raw, multi-source, messy data into clean, actionable insights.
Ready to go beyond the basics and truly master advanced data transformation with Power Query and Microsoft Copilot? Enroll in our 'Advanced Excel + Power Query + Microsoft Copilot' course at Excel Logics. Elevate your data skills and become an indispensable asset in your organization.
Originally published at Excel Logics Blog