Pivot Table in Power BI
Pivot Table is simply the tool to create a summarized report from the 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.
How to Create a Pivot Table in Power BI?
Below is the data we are going to use to create a pivot table.
You can download the workbook and can use it to practice with us.
- Upload the data to the Power BI file.
- Come back to the “Report” tab to create a summary table.
- From the visualization, list inserts “Matrix” visual.
This matrix visual has the below fields.
- Rows are nothing but on “Row-wise” what we want to see.
- Columns are nothing on “Colum-wise” what we want to see.
- 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.
- As we can see above we can see “Year, Quarter, Month, and Day”, since we need to see monthly summary cancel all except “Month”.
- Now drag and drop the “Category” column to “Columns”.
Now we could see a table like this.
- Since we need to see a “Sales” column summary drag and drop “Sales” column to the “Values” field of “Matrix” visual.
This will bring us the summary table which looks like the “Pivot Table” in excel.
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 drill down summary. For example assume you want to see the “Sub category-wise” summary for each month and each “Category”, we cannot fields rather insert a slicer visual from the visualization list.
- For this slicer field drag and drop “Sub Category” column from the table.
- Now we could see “Sub Category” names in the slicer.
- 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.
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.
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”.
This will open up the “Power BI Query” editor window.
- Step 2: Choose the table and choose the “Month” column.
- Step 3: Under the “Transform” tab click on “Pivot Column”.
This will open the “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.
- Step 5: Now click on Ok and it will pivot the columns.
- Step 6: Click on “Close and Apply” to see the real result.
The real result is shown below.
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.
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 “Year” column.
- Step 2: Under “Transform” tab click on “Un-Pivot >>> Un-Pivot Other Columns”.
This will un-pivot 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.
Things to Remember
- Pivot Table is not the tool available in Power BI but using matrix visual 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
