What is Pivot Table in Power BI?
A Power BI Pivot table visualizes data that allows you to summarize and analyze large datasets. It provides a flexible way to organize and aggregate data by grouping it into rows, columns, and values. You can perform calculations, apply filters, and drill down into the data to gain meaningful insights and decision-making. You can create Power BI Pivot tables using visualizations such as Matrix or Table, which offer functionality similar to traditional Excel pivot tables.
Some of the key benefits of using the Pivot table in Power BI include:
- Easy data comparison across datasets
- Summarize the dataset and combine duplicate datasets in aggregation and analysis
- Automatic aggregation across rows and columns.
Table of contents
- Power BI provides pivot table-like functionality through visualizations such as Matrix and Table.
- Pivot tables in Power BI allow you to summarize and analyze data by grouping it into rows, columns, and values.
- You can create pivot tables in Power BI by dragging and dropping fields onto the report canvas and applying calculations.
- Pivoting and unpivoting columns in Power BI can help restructure data for better analysis.
- Power BI has some limitations compared to Excel pivot tables and certain features may not be available.
- DAX formulas can be used in Power Pivot, a component of Power BI, but Excel pivot table formulas are not supported in Power BI.
How to Create a Pivot Table in Power BI?
To create a pivot table in Power BI, follow these steps:
- Open Power BI Desktop and connect to your data source.
- Drag and drop the desired fields from your data source onto the report canvas. Map them in the Rows, Columns, and Values areas of the visualization pane to define the structure of your pivot table.
- Apply any necessary calculations or aggregations to the values by using the appropriate measures or functions.
- Customize the appearance and formatting of the pivot table by adjusting properties such as font size, colors, and column widths.
- Apply filters or slicers to refine the data in the pivot table.
- Save and publish your Power BI report to share it with others.
Note that Power BI doesn’t provide any Pivot table visual by default. You can apply the Pivot table in Power BI using Matrix or Table Visual. You can create Pivot table with multiple columns or a Pivot table with multiple rows. Using the Table visual in Power BI, you can create a Pivot table visual with the related dataset. Similarly, you can apply various formatting options to a dataset to ensure they are aligned to the Pivot table tabular format.
In the subsequent section, we will go through some of these examples to understand how you can create these Pivot tables in Power BI.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
In this section, we will see two examples where we provide a step-by-step process to create a Pivot Table in Power BI using Matrix and Table Visual.
In this example, we will create a Power BI Pivot table with multiple rows and one with multiple columns using the US Superstore dataset. The US Superstore dataset contains insights into online orders of a superstore in the US. To create this Power BI Pivot table, follow these steps:
Step 1: Open the Power BI Desktop and import the US Superstore dataset using Data Connection.
Step 2: Navigate to the Visualization pane and select Matrix Visual in Power BI.
It will create a blank Matrix visual in the report canvas.
Step 3: Navigate to the Fields pane, select the data fields, drag, and drop the relevant data fields into the Visualization pane to the Rows, Columns, and Values section.
In this case, we have mapped the following data fields for Matrix Visual.
- Rows – City, Segment, and Category
- Columns – Region
- Values – Total Sales
It will create a Power BI Pivot table in Matrix visualization format.
Step 4: Navigate to the Visualization pane and select the Slicer visual.
Step 5: Drag and drop the data field to the Field section for the Slicer filter. Here, we have mapped the City field to the Field section to apply the filter on City values.
Step 6: Format the Slicer using the options in the Visualization pane. We have used the formatting options such as Slicer Style to dropdown with select all option.
It will create a Power BI Pivot table with Matrix visual along with a Slicer option where you can apply filters on various US cities to view the gross sales across different categories and segments.
As you can see, we have created a Power BI Pivot table with multiple columns and a Power BI Pivot table with multiple rows. The Power BI Pivot table with numerous rows can be further expanded to see the gross sales more granularly, such as Categories and Segments.
Using the US Superstore dataset in this example, we will create a Power BI Pivot table with a tabular format. We will make this Pivot table using Pivot table visual. To create the Pivot table, follow these steps:
Step 1: Open the Power BI Desktop and import the US Superstore dataset using Data Connection.
Step 2: Navigate to the Visualization pane and choose the Table visual icon.
You will see a blank table visual created in the report canvas of Power BI.
Step 3: Now drag and drop the data fields from the Fields pane into the Columns section in the Visualization pane.
Here we have added City, Segment, Category, Region, and Total Sales to the Columns section of the Visualization pane.
Step 4: Navigate to the Visual tab under Format your visual section in the Visualization pane and apply the formatting options on the Pivot table visual.
Here we have formatted the Style presets to Alternating rows and Bold font to the Column headers.
Now post formatting, you will see the Power BI Pivot table in tabular format displayed in the report canvas.
Step 5: Navigate to the Visualization pane and choose Slicer Visual. Add the formatting option to customize the Slicer visual.
This will create a Pivot table visual in the report canvas.
Pivot and UnPivot Columns in Power BI Table
Power BI supports both Pivot and Unpivot columns through the Power Query tool. For illustration purposes, we have created a table with country-wise Tax information across 2 consecutive years.
To Pivot columns in Power BI Table, follow these steps:
Step 1: Import the Tax Rate table into Power BI and select Transform Data.
It will load the data in the Power Query tool.
Step 2: Navigate to the Transform tab, choose the column to be transformed, and then select the Pivot Column option from the toolbar.
Step 3: Choose the Values Column from the dropdown and provide advanced options for applying any aggregation. Here we have selected the Don’t Aggregate option.
It will pivot the columns in the Tax Rate table.
Step 4: Select the Close & Apply option. This will save the changes to the table.
You can see the Tax Rate dataset is now changed with Pivot Column in the Fields pane.
Similarly, you can also Unpivot columns in the Power BI table. We will see how you can Unpivot Columns on the Tax Rate table. To Unpivot Columns, follow these steps:
Step 1: Right-click on the Tax Rate table or click on More options (…) in the Fields pane.
This will open a menu bar.
Step 2: Select Edit query from the menu. This will open the dataset in the Power Query tool. Navigate to the Transform tab and select Unpivot Columns.
This will Unpivot the columns to the original Tax Rate table structure.
Step 3: Now select the Close & Apply option to save the changes.
Post the changes, you can see the Unpivot columns in the Tax Rate table in the Fields pane.
Important Things to Note
- Power BI doesn’t support the data refresh of workbooks, so you may need to reload your pivot table each time you view it.
- Power BI provides various visualization options, including pivot tables, matrices, and tables with similar functionality. These visualizations can be used to achieve the desired analytical outcomes.
- Pivot tables in Power BI do not have all the features and capabilities of Excel pivot tables. Some advanced Excel pivot table features may not be available in Power BI.
- Power BI is primarily designed for importing and analyzing data, rather than directly connecting to existing pivot tables or external files.
- While Power Pivot, a component of Power BI, supports DAX formulas for creating calculated columns, measures, and tables, traditional Excel pivot table formulas cannot be used in Power BI.
Frequently Asked Questions (FAQs)
There are 2 options to import a pivot table into Power BI:
• Option 1: Import the Pivot table stored in an Excel file in Power BI using the Data Connection. Once the data is imported, you can use the dataset as a data source for any of the reports or dashboards.
• Option 2: Import a dataset into Power BI using Data Connection and then create a Pivot table in Power BI using Matrix or Table visuals in the Visualization pane.
Yes, you can use the DAX formula in Power Pivot in Power BI. Power Pivot is a component of Power BI that allows you to create data models and perform calculations using DAX (Data Analysis Expressions) formulas. Using DAX formulas in Power Pivot to define calculated columns, measures, and calculated tables. These calculations can then be utilized in Power BI reports and visualizations.
Yes, you can use a Pivot Table as a data source in Power BI. However, to do so, you must import the Pivot Table dataset into the Power BI tool. Once the dataset is imported, you may use it for your analysis. Otherwise, it’s not directly possible.
If your Power BI Pivot Table is not working, here are a few common troubleshooting steps to consider:
• Ensure that the data source you’re using is correctly connected and contains the necessary data for the Pivot Table.
• Review the field settings in the PivotTable to ensure that the appropriate fields are selected for the rows, columns, and values.
• If your underlying data has changed, refresh the data in Power BI to update the Pivot Table.
• If you’re using multiple tables in your data model, check the relationships between them to ensure they are correctly defined.
• Ensure that you’re using the latest version of Power BI and that any required updates are installed.
• Look for any error messages or warnings in Power BI that might provide insights into the issue.
This has been a guide to Power BI Pivot Table. Here we provide a step-by-step process to create a Pivot Table in Power BI using Matrix and Table Visual. You can learn more from the following articles –