Convert a Power BI Matrix to Tabular format to recreate the look of an Excel Pivot Table and learn when a table visual might be a better choice to present your data. We’ll explain how to update a Matrix layout, and some key considerations to re-creating additional pivot table type formatting in Power BI.
Depending on the exact formatting, users may need to consider using a table visual even though it has many formatting limitations.
The example below shows the standard Matrix view of data on the left, and a tabular view on the right.
By default, Power BI will place all Matrix visuals in a compact format, making it more difficult to read. While this recreates much of the functionality of a Pivot Table, users will be unable to repeat rows for a full table view.
To Switch a Power BI Matrix Visual from Compact to Tabular Format, select the Matrix Visual. Go to the Visualizations Tab > Format Your Visual > Row Headers > Options and disable Stepped Layout. Users can alternatively search for “Stepped Layout” in the Matrix formatting options to find the setting faster.
We’ll look at some additional settings that are good to be aware of when working with Power BI Matrix Tabular Formats and Tables.
How to Create a Matrix in Power BI
To create a matrix in Power BI, select the Matrix Visual from the visualizations tab. Drag and drop fields from the Data panel to the columns, rows, and values to populate it similar to an Excel Pivot Table.
What is Power BI Matrix Tabular Formatting?
Tabular format organizes data into rows and columns. It makes the presentation similar to how people are used to look at tables, spreadsheets, or databases. Each row represents an individual record. Columns are fully separated instead of being nested or indented below the categories they belong to.
Change a Power BI Matrix to Tabular Format with Stepped Layout
To change a Matrix to a Tabular Format, select the Matrix Visual and navigate to Format Your Visual.
Navigate to Row Headers > Options > Stepped Layout
Click the on / off button to disable stepped layout. This will put each level of your column hierarchy into individual columns instead of nesting or indenting them under the level above it.
Tip: Because it’s fairly deep in the visualization options menu, we often search for it under the format visual settings.
The hardest part is remembering the name of what Microsoft decided to call it!
Change a Power BI Matrix to Outline Format
You can use the stepped layout options in Power BI to re-create a Pivot Table Outline Format by going to: Visualization Format > Row Headers > Options > Stepped Layout and adjust the stepped layout indentation. Increase the stepped layout indentation until the columns are separated.
There are some limitations with using the stepped layout indentation options.
- You can only indent up to 100 px, which could be too short for some columns.
- Both column groups remains in a single column making it more difficult to work with later.
Even with these limitations it is a quick way to recreate a Pivot Table Outline Format in Power BI without much effort.
Repeat All Item Labels in Power BI Matrix
Unfortunately, the Power BI Matrix visual does not allow you to repeat all item labels like you would in an Excel pivot table. Instead of repeating all item labels, users must switch from the Matrix Visual to the Table Visual. Changing the format is easy, select the Matrix Visual and select the Table Visual to the left of it on the visualizations panel.
The biggest drawback to this is that the Table visual is severely limited in formatting options when compared to the Matrix visual.
Let’s look at the difference between the two visuals in more detail.
Power BI Table vs Matrix Visualizations
When deciding whether to use a Table or Matrix visual in Power BI, it’s good to think of one as a .csv file and another as a pivot table in an .xlsx file. The Matrix provides much more functionality and flexibility for layouts, subtotals and formatting. The Table Visual is a quick way to display data in a tabular format with minimal additional features.
As you can see from the picture above, the Table Visual looks like a tabular formatted matrix with rows repeated. In many cases this could be exactly what you need, but it’s important to note the functionality that is not available when working with tables.
We’ve broken down what is and is not available in each.
| Feature | Table Visual | Matrix Visual |
| Basic Structure | Rows and Columns | Rows, Columns, and Values |
| Dimensionality | Single dimension (rows) | Multi-dimensional (rows and columns) |
| Hierarchies | Not supported | Supports row/column hierarchies |
| Aggregations | Can display aggregated values | Can display aggregated values, with subtotals and grand totals |
| Drill Down/Up | Not supported | Available with hierarchies |
| Expand/Collapse Functionality | Not supported | Available with hierarchies |
| Layout Customization | Limited to row sorting | Flexible row/column positioning |
| Visual Interactivity | Basic selection and highlighting | Advanced with hierarchy drill-through |
| Best For | Listing individual records or simple aggregated metrics | Cross-tabular reports, pivot-like reports |
It’s worth trying out by the table visual and matrix visual to see if you can live with the limitations in formatting.
Tip: Copy and Paste a Matrix Visual to a new tab before changing it to a table visual. This lets you test if it will work without losing any of the formatting work that you have already done.
As you’re exploring Power BI and learning about the different visuals you’ll find that there’s almost an infinite amount of options. Check out these quick tips and tricks to help you get even more out of Microsoft’s premier business intelligence product.
Conclusion
Turn stepped layout off under visualization format options in Power BI’s Matrix settings to re-create a tabular form pivot table. To repeat rows, users will need to use a table visual at the expense of many advanced formatting and navigation features.