WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn Power BI » Power BI Pivot Table

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.

Popular Course in this category
Sale
Power BI Training Certification
4.5 (521 ratings)
6 Online Courses | 29+ Hours | Verifiable Certificate of Completion | Lifetime Access
View Course

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 –

  • Parameters in Power BI
  • Bookmarks in Power BI
  • Timeline in Power BI
  • Waterfall Chart in Power BI
0 Shares
Share
Tweet
Share
Power BI Training Certification
  • 6 Online Courses
  • 29+ Hours
  • Verifiable Certificate of Completion
  • Lifetime Access
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Download Power BI Pivot Table Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Power BI Pivot Table Excel Template

New Year Offer - Power BI Training Certification (22+ Hours of Training) View More