Power BI Pivot Table

Pivot Table in Power BI

Pivot Table is simply the tool to create a summarized report from a large set of databases. This is the thing we all have done in MS Excel. In such a way, can we use the pivot table in Power BI as well as 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 don’t have a pivot table but like similar visuals to show the summary report.

Power BI Pivot Table

How to Create a Pivot Table in Power BI?

Below is the data we are going to use to create a pivot table.

Power BI Pivot Table (Data)

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

You can download this Power BI Pivot Table Excel Template here – Power BI Pivot Table Excel Template
  • Upload the data to the Power BI file.

Power BI Pivot Table (Data tab)

  • Come back to the “Report” tab to create a summary table.

Power BI Pivot Table (Report)

  • From the visualization, the list inserts “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 on “Row-wise” what we want to see.
  2. Columns are nothing on “Colum-wise” what we want to see.
  3. Values, i.e., which column values we need to see as a summary table.
  • For example, assume we want to see “Monthly” “Category-wise” sales value. Fist drag and drop the “Date” column to the “Rows” field.

Power BI Pivot Table (Order Date)

  • As we can see above, we can see “Year, Quarter, Month, and Day” since we need to see monthly summary cancel all except “Month.”

Power BI Pivot Table (Order month)

  • Now 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)

  • Since we need to see a “Sales” column summary, drag and drop the “Sales” column to the “Values” field of the “Matrix” visual.

Power BI Pivot Table (Sales)

This will bring us the summary table, which looks like the “Pivot Table” in excel.

Power BI Pivot Table (Summary Table)

This isn’t the pivot table but a kind of summary table by 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 the “Sub category-wise” summary for each month. Each “Category” we cannot fields rather insert a slicer visual from the visualization list.

Power BI Pivot Table (Table slicer)

  • For this slicer field, drag and drop “Sub Category” column from the table.

Power BI Pivot Table (Slicer sub-category)

  • Now we could 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” subcategory 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 pivot table kind of summary table.

Pivot and Un-Pivot Columns in Power BI Table

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

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 appearing 12 times for 12 months, so this is a repetitive task. So we will pivot these columns and create a short summary table.

  • Step 1: Upload the file to Power BI, under the home tab, click on “Edit Queries.”

Power BI Pivot Table (Edit queries)

This 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

This 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 together.

Values column

  • Step 5: Now click on, Ok, and it will pivot the columns.

Pivoted Column

  • Step 6: Click on “Close and 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 names and month names to have a simple table, so by using this Pivot Column option, we can manipulate the data.

Similarly, we can un-pivot 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 Power, Query window, choose the table, and choose the “Year” column.

Year column

  • Step 2: Under the “Transform” tab, click on “Un-Pivot >>> Un-Pivot Other Columns.”

Un-pivot Other Columns

This will un-pivot columns.

Un-pivoted Columns

Like this, we can use Power Query editor to pivot and un-pivot columns.

Note: Power BI file can also be downloaded from the link below and the final output can be viewed.

You can download this Power BI Pivot Table Template here – Power BI Pivot Table Template

Things to Remember

  • 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.

Recommended Articles

This has been a guide to Power BI Pivot Table. Here we discuss how to create a pivot table in power bi. We also learn how to pivot and un-pivot columns using a power query along with examples. You may learn more about Power BI from the following articles –

  • 6 Online Courses
  • 29+ Hours
  • Verifiable Certificate of Completion
  • Lifetime Access