Pivot tables are an essential tool for data analysis and can be used to make complex data easier to understand. Power BI is a powerful tool that helps you quickly summarize, analyze, explore, and present your data in an interactive way.
Power BI does not have the ability to create traditional Pivot Tables. Power BI developers must use the Matrix Visualization to provide a similar presentation of data. Matrix Visuals are highly customizable and have features that are not available in Excel Pivot Tables.
As an alternative, some Power BI developers will connect Microsoft Excel to an existing Power Dataset to leverage the benefits of Power BI while still providing end users with a familiar spreadsheet based interface.
Let’s take a look at the Power BI Pivot Table Alternative, the Matrix Visual.
Table of Contents
Adding a Matrix Visual as a Pivot Table Substitute in Power BI
In our example, we are going to use Power BI Desktop.
You could build a similar report using the cloud version of Power BI available at powerbi.com. Microsoft has been adding new features and is an easy alternative for users creating reports on a Mac. There are a number of limitations compared to Power BI Desktop like the inability to create custom DAX measures.
After brining data into your Power BI Report, click on the Matrix Visual button.
The Matrix visual looks like a small table with columns and rows highlighted in blue.
Note: The Table tool to the left has some overlapping functionality. It is only designed to present rows of data. The Table Visual does not give you the formatting options and flexibility of a Matrix Visual.
Click on the Matrix Visualization
This will insert a blank visual outline into your Power BI dashboard.
You can drag and drop the outline anywhere you want your visual to be.
Next, We’ll add data to our visualization
Drag and Drop column or field names from the Data Panel on the right side of Power BI.
You can drag them to rows, columns, and values.
Similar to how you would setup a Pivot Table in Microsoft Excel.
After you add columns, rows and values to the Matrix visual it will populate with data. You can drag and drop each field to move them around.
Click the X to the right a field name to remove it.
This re-creates the basic functionality and view of an Excel Pivot Table in Microsoft Power BI.
Now let’s look at some other cool stuff you can do!
Changing Aggregation Types Like A Pivot Table
Changing between sum, count, average is very easy within Power BI. This works almost identically like a Pivot Table.
The Values section of the Matrix Visual has a small down arrow to the right of the field name.
If you click on the down arrow, a contextual menu appears where you can select different aggregation methods.
Power BI gives users the ability to Sum, Average, Min, Max, Count Distinct, Count, Calculate Standard Deviation, Variance and Median.
Count Distinct is one of our favorites that’s not available in Microsoft Excel. It will only count a value once even if it occurs multiple times in a dataset!
The “Show Value As” Section lets you show percentage of grand total, column total, or percentage of row total.
Adjusting Matrix Visual Formatting in Power BI
After creating a Matrix Visual, you can format it to add subtotals, turn off multiple levels of subtotal, adjust the title font size and format etc.
All of these options are available under the Format Visual Panel
If you’re unsure of which section the format option is under, we use the search box a lot. It’s by far the fastest way to navigate through the large number of formatting options available.
In this section you can perform a number of common tasks.
- Turn off subtotals for rows and columns
- Apply per column level subtotals – This allows you to disable the matrix showing multiple columns or rows of subtotals when a user expands the data table.
- Adjust header formatting
- Switch Values to Rows – This option pivots the Matrix visual
- Turn Stepped Layout on and Off – Toggle between nested rows or a more tabular format like a pivot table
And much more!
The number of formatting options are almost limitless.
You can even click on an individual field and apply conditional formatting to it!
Click on a field value, and select conditional formatting. It will bring up an entirely new screen where you can create dynamic formatting based on formulas and various color scales.
Most of what you can do in Excel Pivot Tables, you can do better in Power BI with the added benefit of it being fully dynamic. Datasets will automatically refresh, and users can explore the data however they want without creating multiple copies of the same spreadsheet.
Formatting Values and Sorting Matrix Visual Columns
To format the values within a Matrix Visualization, click on the column name or field that you would like to update from the Data Panel on the right side of Power BI. By selecting a field, you will see a new Column Tools menu at the top ribbon of Power BI.
The Column Tools ribbon lets you adjust the number formatting of a Power BI Matrix
Here are some of the most common tasks you can do with Column Tools.
- Adjust Decimal Places
- Add Commas or Dollar formatting in a Power BI Report
- Sort a column by the values in another column
- Switch from whole numbers to decimals
- Quickly switch column data types
- Create custom groups for values in a column
These options are available for most visuals, but they may not all be available in some charts or graphs.
You will have to update each column individually using this method.
If you need to update many columns at once, it may be quicker to select multiple columns using the Power Query editor and update their format. The format updates will be universal for all visuals that have not had individual customizations performed.
Note: If column tools are greyed out, you may be working with a live connection to a dataset. Recent updates to Power BI have improved this limitation by allow users to create a local copy of a data source.
Working With Dates and Expanding Columns
When date columns are added into the Power BI Matrix visual, you can expand the dates and adjust date hierarchy to meet your reporting needs.
You can Right Click a Column or Row and Expand to Next Level
In the Example below, we clicked on Expand to Next Level in the Matrix Visual and it will follow the date hierarchy shown in the columns field.
The Matrix Visual is fully interactive for the end user. However, you can control the data that they can see.
If you click the X to the right of the Quarter in the example, the Expand to Next level will skip that level and allow you to go from Year to Month.
If you do not have a date hierarchy available, your data may not be setup as a date data type in Power Query. Launch the Power Query Editor, right click the column header, and change type to date. Power BI will only show date hierarchies and allow date intelligence for date data types.
Users can also navigate drilling up and down using the up and down arrows above the Matrix Visual.
The line with two arrows at the bottom expands the visual.
The difference is that expanding will keep the hierarchy levels as column headers. When you drill down it will only show the next level of the hierarchy.
Renaming Matrix Visual Columns
All of the columns in a visual can be renamed.
To rename a column header, double click on the name of the field in the values section.
When you double click on it, it becomes selectable and you can type over it.
Changing a field name will only impact the specific visual that it’s related to.
You could also change a column labels by renaming them in the Data Panel to the right. Changes on the right will flow through to all visuals if you have not already renamed them.
Tip: If you want to hide the name of a column in your visual, you can replace the name with a space. It will make it look like the column has no title.
Filtering Power BI Visuals with Slicers
Power BI allows end users of reports to filter data using a functionality called slicers.
Slicers have been in Microsoft Excel for years, and it’s a similar concept in Power BI.
In Power BI you add a secondary visual on the same page page as the Power BI matrix visualization.
Select the Slicer Visual
Drag and Drop one of the fields you would like to filter by onto the slicer
Users will be able to click on the slicer to filter and change the view on the report
Slicers will adjust how users view the entire dashboard. It will filter the matrix visual along with any other visualizations you have on the page.
You can even link slicers to different tabs or pages of your Power BI report.
The great thing about slicers is that they change the view, but they do not impact the underlying data. This means that different users can filter reports however they want and you don’t have to worry about someone saving the report with their own settings and throwing off your Excel Pivot Table for the next user.
Slicers are an incredibly powerful functionality of Power BI. The following video from Guy in a Cube gives a great overview of the functionality and how to improve your overall dashboard design when integrating them into yoru reports.
The more that you develop with Power BI, the more you will utilize slicers and find cool new tricks!
Exporting Data to Excel from a Power BI Visual
One of the best features of Power BI is that it allows users to quickly export data from a visual to an Excel spreadsheet.
Many users really just want the data, and it gives them the flexibility to explore the data and analyze it any way that they want.
When exporting data, it will give you the ability to save the data as a .csv file.
Data is exported in a columnar format that can also be quickly placed into an Excel Pivot Table.
If you do not want people exporting data, you can turn off the ability to do so on the PowerBI.com workspace settings.
Connecting Excel to a Power BI Dataset
Users can connect Microsoft Excel directly to a Power BI dataset and utilize a regular Excel Pivot Table with a live connection to data that has already been prepared in Power BI.
You will not have all of the functionality that you would have in Power BI, but it provides users with a familiar Excel experience without a developer having to create multiple data models.
To connect Excel to a Power BI dataset follow these steps:
- Create a new Excel Spreadsheet (Must be logged in to same Microsoft Account as Power BI)
- Click on Insert > Pivot Table > From Power BI
- Select the previously published Power BI dataset
After doing so, you will be able to interact with a Power BI dataset in Excel like you would with a normal Pivot Table.
Below is an example of Excel connected to the underlying data of a Power BI dashboard.
The updates are not as fast or smooth as having a report published in Power BI and users accessing it through the Power BI service, but we see it as a great transitionary tool for managers who are reluctant to transition to new ways of viewing legacy reports.
We hope these tips and tricks have been helpful for your transition from Excel to Power BI.
While there is no direct Power BI Pivot Table function, you can re-create and exceed the functionality of a pivot table with the included Power BI Matrix Visualization.
With a little bit of training, companies should be able to mitigate the need for Excel spreadsheets by using Power BI. However, if business users are reluctant to learn Power BI, they can still answer many of their questions with a traditional Excel pivot table that is connected directly to a Power BI datasource.
By utilizing the same data source, developers only have to manage one data model. This prevents people from seeing different data across different report formats.