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.
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.
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.
2. Under the visualization panel, select the Matrix visual.
3. Under the visualization panel the matrix visual has the below fields.
- 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.
5. Next, drag ‘Customer Name‘ into the Rows field, ‘State‘ into the Values field, and ‘Sales‘ into the Columns field. Check the screenshot below.
6. This will generate the matrix visual; it looks like a Pivot Table.
7. You can use drill down for more detailed information.
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.
In Power BI, pivot functionality turns rows into columns, and unpivot functionality does the opposite, turning columns into rows.
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.
2. Under the Home tab, click Transform data.
3. Select the Category column, then right-click on it and click Unpivot Other Column.
4. It will unpivot columns. Check the screenshot below.
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.
6. Select the Attribute column, and Under the Transform tab, click Pivot Column in the power query editor.
7. It will open the Pivot Column dialog box. We need to choose the Values Column as Value. Then click OK.
8. Now see the below un-pivot column convert pivot column.
This way, we can use the Power Query Editor to pivot and unpivot columns.
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:
Preeti Sahu is an expert in Power Apps and has more than 6 years of experience working with SharePoint and the Power Platform. As a Power Platform expert for Power BI, Power Apps, Power Automate, Power Virtual Agents, and Power Pages, she is currently employed with TSinfo Technologies. She is the author of the book Microsoft Power Platform A Deep Dive. She also made a big technical contribution to SharePointDotNet.com in the form of articles on the Power Platform. She enjoys traveling and spending time with her family in her spare time.