Yes. In Power BI Desktop, you can pivot data in Power Query by choosing a column whose distinct values should become new column headers, then selecting the column that supplies the values. If you only want a cross-tab in a report without reshaping the underlying data, use a Matrix visual instead.
What pivoting means in Power BI
Pivoting changes the shape of a table. Values that appear down one column become separate column names, and the values from another column fill the resulting cells. The other columns in the table determine which records are grouped into each row.
For example, a source table might contain one row for each date, product, region, and sales amount. Pivoting the Product column using Sales as the values column produces a table with Date and Region as row identifiers and one column for each product. The sales amount for each date-region-product combination goes into the matching cell.
This is a data transformation, not just a display choice. Power Query changes the table it loads into the Power BI model. That can be useful when a downstream report or export needs a wide table, but it can also make the data less flexible for analysis if you create many product-specific columns.
How the Power Query pivot works
When you pivot a column, Power Query uses the distinct values in that column as the new column headers. It then groups records by all the other columns that remain in the query, except for the column chosen to provide cell values. This grouping behavior is important: an unintended extra column can produce more rows than expected, while removing a necessary identifier can combine records that should stay separate.
Suppose the source includes Date, Region, Product, Sales, and Salesperson. If Product is pivoted and Sales is selected as the values column, Salesperson remains in the table unless you remove it. The output will then be grouped by Date, Region, and Salesperson, not just Date and Region. Review which columns should identify a row before applying the transformation.
Power Query also has to decide what to do when more than one source record maps to the same output cell. If there is one record per row-and-column combination, it can place that value directly. If there are duplicates, you may need an aggregation such as Sum or Count, or you may need to correct the source data first.
Steps to pivot a column
In Power BI Desktop for Windows, open the Power Query Editor with Home > Transform data. Select the query and check that its columns and data types are correct. Then:
- Select the column whose values should become new headers, such as Product.
- Choose Transform > Pivot Column. Depending on the editor layout, you may also be able to right-click the selected column and choose Pivot Column.
- In the dialog, choose the column containing the values to place in the cells, such as Sales.
- If needed, expand Advanced options and choose an aggregation method for duplicate combinations.
- Select OK, inspect the resulting columns and rows, and use Home > Close & Apply to load the transformed query into the model.
Power Query records each transformation as an applied step. You can select a step to inspect its result, edit its settings where available, or remove it from the Applied Steps list. That makes it possible to revise a pivot without rebuilding the entire query, although changing earlier steps can affect later ones.
A practical example
Imagine this source data:
- 2026-01-01, North, Desk, 120
- 2026-01-01, North, Chair, 80
- 2026-01-01, South, Desk, 95
The fields are Date, Region, Product, and Sales. Pivot Product and choose Sales as the values column. The result has Date and Region as identifiers, with Desk and Chair as columns. North has 120 under Desk and 80 under Chair; South has 95 under Desk and a null under Chair because no matching source record exists.
That null does not necessarily mean sales were zero. It means the input did not contain a row for that combination. Before replacing nulls with zero, confirm that absence really means no activity rather than missing or incomplete data. This distinction can affect totals and business interpretation.
Choosing an aggregation method
When multiple source rows share the same remaining row identifiers and the same pivot value, Power Query must resolve those rows. The aggregation choices available depend on the data and editor version, but commonly include Sum, Average, Minimum, Maximum, Median, Count, and a choice not to aggregate.
Choose a method that reflects what the values represent. For transaction amounts, Sum is often appropriate when the intended output is a total. For a measurement where each repeated reading should contribute equally, Average may fit. Count answers how many records fall into a group; it does not add the numeric values. If duplicates indicate a data-quality problem, aggregating them can hide the issue, so investigate before deciding.
Choosing not to aggregate works when each output cell has only one matching source value. If duplicate combinations exist, Power Query may report that it cannot determine a single value. Do not choose Sum simply to make the error disappear unless summing those records makes sense for the analysis.
Pivoting versus a Matrix visual
A Power BI Matrix visual can show categories as rows, columns, and values, creating a report that looks like a pivot table. It is usually the better choice when the goal is to let report users compare categories, expand hierarchies, filter results, or interact with the data while retaining a normalized model.
Power Query pivoting is different: it changes the table before it is loaded. Use it when a downstream process needs a wide table, when a fixed set of categories must become fields, or when you have a specific transformation requirement. Use a Matrix when you mainly want a flexible report layout. A matrix can respond to slicers and measures; a pivoted table has columns created during data refresh and is not an interactive substitute for every matrix feature.
For example, if product categories change over time, a Matrix can display the categories present in the model without requiring you to create a new query column for each one. A Power Query pivot can also create columns from category values, but newly appearing values may change the table schema at refresh.
Pivoting and unpivoting
Unpivoting reverses the general direction of a pivot. It turns multiple columns into attribute-value rows. For instance, a table with columns for January, February, and March can be unpivoted into rows containing a Month field and an Amount field. In Power Query, select the columns to reshape and use Transform > Unpivot Columns, or select identifier columns and use Unpivot Other Columns.
Unpivoting is often useful when a source arrives in a report-style layout but analysis requires a consistent field for category or period. A table with one row per customer and a separate column for every month is wide; unpivoting makes it easier to build time-based visuals and measures. Pivoting and unpivoting are complementary tools, but neither is automatically the right choice: the shape should suit the intended model and reporting task.
Common problems and how to avoid them
- Unexpected extra rows: An extra column is still present and is being used to group records. Remove columns that are not needed to identify output rows before pivoting.
- Duplicate-value errors: Multiple records map to one cell. Check whether the duplicates are valid transactions that need aggregation or errors that should be fixed upstream.
- Missing categories or nulls: A category may not occur for every row identifier. Confirm whether null is the correct representation before replacing it.
- New columns after refresh: Pivot headers come from the values in the source column. A new category can add a column, and a removed category can remove one. Check dependent visuals, measures, or exports if categories vary.
- Unexpected totals: The chosen aggregation may not match the meaning of the source values. Validate a few groups against the original records, especially when summing repeated or already summarized data.
Also check data types before pivoting. A values column should have a type compatible with the intended aggregation; text values, for example, cannot be summed like numeric values. If a pivot operation fails, review the error details and the earlier steps that set types or filter records rather than assuming the pivot command itself is faulty.
Deciding whether to pivot in Power Query
Consider the output’s purpose before reshaping. A fixed, compact set of categories and a requirement for one field per category can make a pivot practical. A long, changing list of categories, or a need to slice and aggregate dynamically, generally favors keeping the data in rows and using a Matrix or other visual.
Remember that a wide table can become difficult to maintain as categories multiply. It may also make it harder to write reusable measures across categories, because each category is represented by a separate field. Conversely, pivoting can simplify a specific export or satisfy a source-system format requirement. Test the result with representative refreshes and verify both the row identifiers and the totals before relying on it.
Frequently Asked Questions
Can I pivot data in Power BI without writing code?
Yes. In Power BI Desktop, use Home > Transform data to open Power Query Editor, then select the category column and choose Transform > Pivot Column. The dialog lets you select the values column and, when necessary, an aggregation.
What is the difference between Pivot Column and a Matrix visual?
Pivot Column reshapes the query table before it is loaded into the model. A Matrix arranges model data for display in a report and remains interactive. Choose based on whether you need a changed data structure or a cross-tab presentation.
Why does Power Query ask me to aggregate values?
More than one input record maps to at least one output cell. Select an appropriate aggregation, such as Sum, or investigate whether the repeated records reveal a data issue. If each combination should be unique, correcting the source may be preferable.
Can Power BI automatically create a column for each new category?
A pivot can derive headers from the distinct values present in the source during refresh. New values may create new columns, but that schema change can affect dependent steps, visuals, or other consumers of the table.
Should I pivot or unpivot my data?
Pivot when category values need to become fields for a particular output. Unpivot when multiple category-specific fields should become rows in a consistent attribute-value structure. For interactive report layouts, consider a Matrix before changing the model table.
Related
- Energy Audit Template in Excel: What to Include
- How to Indent Text in an Excel Cell
- Encrypt an Excel File with a Password: Steps and Tips
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Power Pivot: Create a Table and Build a Data ModelTo create a table in Excel Power Pivot, first make or import a structured data table in Excel,…
- Excel Histogram: Add Bins and Control Their WidthsTo add or change bins in an Excel histogram, select the chart’s horizontal axis, open Format Axis, and…
- Packed Bubble Chart in Excel: Options and AlternativesExcel for Microsoft 365 does not include a native packed bubble chart. You can make a standard bubble…
- Excel Histogram with Multiple Series: Methods That WorkExcel’s built-in Histogram chart is designed for one data series at a time, so selecting several columns will…
- Excel Line Chart Trendline: Add, Format, and ChooseTo add a trendline to an Excel line chart in Microsoft 365 for Windows, select the chart, then…
- Excel Pivot Chart: Change Legend Label NamesTo change a PivotChart legend label in Excel for Microsoft 365 on Windows, change the caption of the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.