Power BI Pivot Table

Last Updated :

-

Blog Author :

Jeevan A Y

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

Pivot Table in Power BI

PivotTable is simply the tool to create a summarized report from a large set of databases. It is the thing we all have done in MS Excel. So in such a way, can we use the pivot table in Power BI and the common question everybody asks? So in this article, we will show you how to use the “Pivot Table” kind of analysis in Power BI. But in Power BI, we do not have a PivotTable but similar visuals to show the summary report.

Table of contents
Power-BI-Pivot-Table

How to Create a Pivot Table in Power BI?

Below is the data we will use to create a PivotTable.

Power BI Pivot Table (Data)

You can download the workbook and can use it to practice with us.

  • Upload the data to the Power BI file.
Power BI Pivot Table (Data tab)
  • Return to the “Report” tab to create a summary table.
Power BI Pivot Table (Report)
  • From the visualization, the list inserts the “Matrix” visual.
Power BI Pivot Table (Matrix)

This matrix visual has the below fields.

Power BI Pivot Table (matrix visual Fields)
  1. Rows are nothing but “Row-wise” what we want to see.
  2. Columns are nothing on “Column-wise” what we want to see.
  3. Values mean which column values we need to see as a summary table.
  • For example, assume we want to see “Monthly” “Category-wise” sales value. First, drag and drop the “Date” column to the “Rows” field.
Power BI Pivot Table (Order Date)
  • As we see above, we can see "Year," "Quarter," "Month," and "Day" since we need to see the monthly summary cancel all except "Month."
Power BI Pivot Table (Order month)
  • Drag and drop the "Category" column to "Columns."
Power BI Pivot Table (Category)

Now, we could see a table like this.

Power BI Pivot Table (Table)
  • As we need to see a "Sales" column summary, we must drag and drop the "Sales" column to the "Values" field of the "Matrix" visual.
Power BI Pivot Table (Sales)

It will bring us the summary table, which looks like the "PivotTable" in Excel.

Power BI Pivot Table (Summary Table)

It is not the PivotTable but a summary table using Matrix visuals. We cannot add any further fields to it, but by using "Slicers," we can view the drill down summary. For example, assume you want to see each month's "Sub category-wise" summary. We cannot field for each "Category" but rather insert a slicer visual from the visualization list.

Power BI Pivot Table (Table slicer)
  • Drag and drop the "Sub Category" column for this Slicer field from the table.
Power BI Pivot Table (Slicer sub-category)
  • Now, we can see "Sub-Category" names in the Slicer.
Power BI Pivot Table (slicer sub-category name).png
  • Now, if we want to see only the "Copiers" sub-category summary table, then choose only "Copiers" from the Slicer, and it will filter the data in the "Matrix" table as well.
Power BI Pivot Table (matrix Copiers).png

Like this, we can create a PivotTable kind of summary table.

Pivot and Un-Pivot Columns in Power BI Table

For example, assume the data you have in your Power BI file below.

Power BI Pivot Table (Pivot data)

As you can see above, it has a year name, month name, and their respective sales figures. In this case, each year appears 12 times for 12 months, so this is a repetitive task. So, we will pivot these columns and create a summary table.

  • Step 1:  Upload the file to Power BI. Under the "Home" tab, click on "Edit Queries."
Power BI Pivot Table (Edit queries)

It will open up the “Power BI Query” editor window.

Power Query Window
  • Step 2: Choose the table and choose the “Month” column.
Choose table query
  • Step 3: Under the “Transform” tab, click on “Pivot Column.”
Pivot column

It will open the "Pivot Column" window.

Pivot Column Window
  • Step 4: From the above window, we need to choose "Values Column" as "Sales" because this is the column we are grouping.
Values column
  • Step 5: Now, click on "OK." It will pivot the columns.
Pivoted Column
  • Step 6: Click "Close & Apply" to see the real result.
Close & Apply

The real result is shown below.

Pivot table in Power query

So, we have eliminated multiple-year and month names to have a simple table, so we can manipulate the data using the "Pivot Column" option.

Similarly, we can unpivot columns as well.

  • Assume below is the table you have.
Un-pivot data

We need to do the reversal of the above. Upload the data to Power BI and open the Power Query Editor.

  • Step 1: From the "Power Query" window, choose the table and the "Year" column.
Year column
  • Step 2: Under the "Transform" tab, click on "Un-Pivot >>> Unpivot Other Columns."
Un-pivot Other Columns

It will unpivot columns.

Un-pivoted Columns

Like this, we can use the "Power Query Editor" to pivot and unpivot columns.

Note: We can also download the Power BI file from the link below and view the final output.

Things to Remember

  • PivotTable is not the tool available in Power BI but uses matrix visuals. We can create a summary table.
  • We cannot expand or collapse items in Matrix visual, unlike MS Excel PivotTable.
  • Pivot Table is not the tool available in Power BI but using matrix visuals, we can create a summary table.
  • We cannot expand or collapse items in Matrix visual, unlike MS Excel Pivot Table.
  • Using Power Query in Excel, we can pivot and un-pivot columns to manipulate the data as per requirement.