Power BI Pivot Table

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.

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.

Examples

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.

Example #1

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.

Example #2

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)

Recommended Articles

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 –

• Power BI Gantt Chart
• Power BI Icons
• Power BI LOOKUPVALUE