Create Pivot Columns Using Power Query

Power Query is one of Excel’s most powerful tools for transforming and reshaping data. One of its handy features is the Pivot Column, which allows you to take values from a column and pivot them into headers, while summarizing data in the process. This is perfect for creating quick summary reports without writing formulas or building Pivot Tables.

Let’s walk through an example step by step.

Key Takeaways:

• Power Query’s Pivot Column transforms values into headers and summarizes them.
• It is perfect for preparing clean, structured datasets.
• Pivot Columns save time by automating repetitive transformations.
• You can apply different aggregations like Sum, Average, or Count.
• Unlike PivotTables, Pivot Columns reshape data for analysis rather than reporting.

 

Understanding Pivot Columns in Power Query

What is Pivoting in Power Query?

In Power Query, pivoting transforms unique values from one column into multiple columns. For example, if you have quarterly sales data listed in rows (Q1, Q2, Q3, Q4), pivoting turns each quarter into its own column, neatly showing the aggregated values. Unlike Excel’s built-in PivotTables, Power Query’s pivoting is about reshaping data rather than interactive reporting. This makes it particularly useful when preparing data for further analysis or dashboards.

Why Use Pivot Columns Instead of Excel PivotTables?

While PivotTables are powerful for analysis, Power Query is a data preparation tool. Pivoting columns here allows you to:

• Clean raw transactional data into a structured format.
• Automate transformations so you don’t need to redo them whenever new data arrives.
• Apply consistent logic (e.g., sum, average, count) without manual intervention.

Essentially, Power Query gets your data “ready for analysis,” while PivotTables are about exploring and reporting.

 

Create Pivot Columns Using Power Query

STEP 1: Our sample data consists of Sales Quarter and multiple sales values for each quarter. We want to sum them all up per quarter. Let us load our data into Power Query.

(Make sure that your data is first converted into an Excel Table by pressing CTRL+T and OK).

Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010)

Excel 2016:

Excel 2013 & 2010:

STEP 2: This will open up the Power Query Editor.

Within here, you need to select Transform > Pivot Column

STEP 3: This brings up the Pivot Column dialogue box.

For the Values Column drop-down, select the column name from our data that has the values in it…in our example, it will be SALES.

Click on advanced options, and this will bring up the Aggregate Value Function.  Here, we select how the new cells should be combined.

As we want to show the Sales Totals for each quarter, we need to select the Sum option from the drop-down box.

Click OK.

STEP 4: Now you will see your changes take place, and the data has now been grouped and summed!

STEP 5: Click Close & Load from the Home tab and this will automatically open up a brand new worksheet in your Excel workbook with the new data.

You now have your new table with the total for each sales quarter.  That’s why they call it POWER QUERY!!!

 

Benefits of Pivoting Data with Power Query

Data Summarization Made Simple

Pivoting columns lets you summarize raw, repetitive data into clear categories. For example, if you’re dealing with quarterly or monthly sales, instead of scrolling through hundreds of rows, you can instantly view totals in a compact format.

This is particularly useful for:

• Financial reports.
• Sales performance analysis.
• Budgeting and forecasting.

Automation and Reusability

Perhaps the biggest advantage is automation. Once you set up a pivot transformation in Power Query, you don’t need to repeat the steps. Simply refresh your query, and new or updated data will be transformed automatically. This means less manual work and fewer chances of making errors—especially important in business reporting where accuracy is critical.

 

FAQs

What is the main difference between Pivot Columns and PivotTables?

Pivot Columns in Power Query reshape and prepare data, while PivotTables in Excel are meant for analysis and reporting. In Pivot Columns, values from one column become new headers, and data is summarized automatically. PivotTables, however, remain flexible and interactive for exploration. If you often need to clean raw data before analysis, Pivot Columns are a better choice. You can use both together for maximum efficiency.

Can I use functions other than SUM when pivoting columns?

Yes, Power Query offers several aggregation functions such as Average, Count, Min, Max, and more. This flexibility allows you to tailor the pivoted results to your specific reporting needs. For example, you can calculate the average sales per quarter instead of the total. Or, you might use Count to see how many transactions happened in each category. Choosing the right aggregation ensures your results match your business question.

What happens if my data has missing values or errors?

If your dataset has missing or blank values, Power Query will still pivot the data but may return nulls in the result. You can replace these nulls with zeros or custom text before or after pivoting. Errors can be handled by applying transformations like filtering, replacing, or conditional logic. It’s always good practice to clean your dataset before pivoting. This ensures accurate summaries and prevents misleading results.

Do I need to repeat these steps every time I get new data?

No, that’s one of the biggest advantages of Power Query—it automates transformations. Once you define your pivot column steps, Power Query records them in its query editor. When you refresh the query, new or updated data is automatically reshaped the same way. This saves significant time, especially for recurring reports. Essentially, you build the process once and reuse it indefinitely.

In what scenarios should I use Pivot Columns?

Pivot Columns are ideal when you need to quickly summarize raw, transactional data. Common use cases include sales reports by quarter, employee attendance by department, or expenses by category. They’re also useful for creating structured tables ready for dashboards or visualizations. Unlike manual formulas, Pivot Columns reduce human error and improve consistency. If your data has repetitive values that need grouping, pivoting is the right tool.