How to Create a Pivot Table in Power BI?

Do you know how to create a pivot table in Power BI? No worries!

This Power BI tutorial explains what a pivot table is and how to create a pivot table in Power BI.

Also, I will explain about Pivot and Un-Pivot Columns in the Power BI Table with various examples.

Pivot Table in Power BI

In Power BI, a Pivot Table is like a dynamic and interactive summary of our data.

For example, you have a large table with lots of information and want to analyze data to understand it better. A Pivot Table helps you quickly summarize data to see patterns and trends.

Refer to the image below for how a Pivot table looks like in Power BI.

How to Create a Pivot Table in Power BI

Note:

Firstly, it’s important to note that there isn’t a specific Pivot Table in Power BI. However, there is a Matrix Visualization that similar purpose to a Pivot Table.

Below is the SharePoint list we will use to create a Pivot Table.

Power BI Pivot Table Data

How to Create a Pivot Table in Power BI

Now I will tell you step by step how to create a pivot table in Power BI.

1. Open Power BI Desktop and Load data into Power BI Desktop.

power bi create pivot table

2. Under the visualization panel, select the Matrix visual.

Power BI Pivot Table using Matrix visual

3. Under the visualization panel the matrix visual has the below fields.

pivot tables in power bi

Where:

  • Rows mean the Row-wise what we want to see.
  • Columns mean the Column-wise what we want to see.
  • Values mean which column values we need to see as a summary table.

4. Now drag the Ship date Rows field. Here, I want to show only the year and then cross the Quarter, Month, and Day.

What is a Pivot Table in Power BI

5. Next, drag ‘Customer Name‘ into the Rows field, ‘State‘ into the Values field, and ‘Sales‘ into the Columns field. Check the screenshot below.

Create a Pivot Table in Power BI

6. This will generate the matrix visual; it looks like a Pivot Table.

Pivot table in Power BI

7. You can use drill down for more detailed information.

Power BI Pivot Table Summary Table

In this way, you can create a Pivot table in Power BI.

Pivot and Un-Pivot Columns in Power BI

Before we start pivoting and unpivoting columns in Power BI, let me explain what these terms mean.

Below is the table we will work with for pivoting and unpivoting columns.

Power BI Pivot Table Pivot data

In Power BI, pivot functionality turns rows into columns, and unpivot functionality does the opposite, turning columns into rows.

Pivot and Un-Pivot Columns in Power BI

How to Pivot and Un-Pivot Column in Power BI

Now, I will tell you how to step by step pivot and un-pivot columns in Power BI.

1. Open Power BI Desktop and put the below table under the Home tab -> Enter data. Then click Load.

what is unpivot in power bi

2. Under the Home tab, click Transform data.

what is pivot in power bi

3. Select the Category column, then right-click on it and click Unpivot Other Column.

Power BI Pivot Table Pivot column

4. It will unpivot columns. Check the screenshot below.

power bi unpivot to new table

Now, let’s explore how to perform the pivot column operation in the table above.

5. Change the data type Text to the Whole Number in the Value column.

Pivot column in Power BI

6. Select the Attribute column, and Under the Transform tab, click Pivot Column in the power query editor.

how create Pivot Columns in Power BI

7. It will open the Pivot Column dialog box. We need to choose the Values Column as Value. Then click OK.

how to unpivot multiple columns in power bi

8. Now see the below un-pivot column convert pivot column.

unpivot other columns in power bi

This way, we can use the Power Query Editor to pivot and unpivot columns.

Conclusion

I hope you follow the steps above to create a Power BI pivot table. This tutorial taught us how to create a pivot table and Pivot and Un-Pivot Columns in Power BI.

Also, you may like some more Power BI tutorials: