How To Create Pivot Table in Power BI

How To Create Pivot Table in Power BI

by

In this blog post, we will walk you through the step-by-step process of creating a pivot table in Power BI. Whether you’re new to Power BI or a seasoned user, this guide will provide you with the knowledge and skills you need to create pivot tables that meet your specific needs.

By the end of this post, you will have a solid understanding of how pivot tables work and how to use them effectively in your data analysis and reporting.

Also, check our 50+ Interview Questions & PL-300 Exam Questions Blog Post.

What is Pivot Table in Power BI?

A pivot table allows you to summarize and analyze large amounts of data by grouping and aggregating information according to specific criteria. You can think of it as a dynamic table that allows you to slice and dice your data in various ways to uncover insights and patterns.

Pivot tables work by allowing you to drag and drop fields into different areas of the table to create rows, columns, and values. For example, you can use a pivot table to group sales data by product, region, and date, and then calculate the total sales for each group.

Pivot tables are highly customizable, and you can change the layout, add or remove fields, and apply filters and sorting to the data. They are also dynamic, meaning that any changes you make to the underlying data will automatically be reflected in the pivot table. This makes pivot tables a powerful tool for data analysis and reporting.

How to Create Pivot Table in Power BI

Creating pivot tables in Power BI is a straightforward process. Here are the steps:

  1. Open your Power BI Desktop and Load the data source. You can connect to different data sources, such as Excel, CSV files, SQL Server, and others.
  2. Once you have Loaded your data source, click on the “Report” tab located on the left-hand side of the screen in Power Bi Desktop.
  3. Select “Matrix Visual” from the “Visualizations” menu.
  4. Choose the fields you want to include in your pivot table. Add fields to “Rows”, “Columns” & “Values” by dragging and dropping the fields from the “Fields” pane on the right-hand side of the screen.
  5. Select the aggregation function you want to apply to your data. You can choose from various aggregation functions such as sum, average, count, etc.
  6. Format your pivot table. You can change the font size, colors, and other formatting options like from the “Visualizations” pane.
  7. Save your report and share it with others in your organization.

That’s it! You now have a pivot table in your Power BI report.

Formatting Matrix Visual / Pivot Table in Power BI

Formatting Display Unit In Pivot Table in Power BI

You can Format the unit of values shown in the visual by navigating to Format your visual in the Visualization Pane. then go to Specific Columns and then select “Display unit”. Click on the dropdown menu and select the display unit that you want to use, such as “None”, “Thousands”, “Millions”, or “Billions”

Sorting Data In Power BI Pivot table

Select the matrix Visual and click on the more options (three dots on the visual). Select “Sort Ascending”, “Sort descending” & “Sort By” as per your requirements.

Adding / Removing Subtotals and Grand Totals

  1. Select the matrix visual that you want to modify.
  2. In the Visualizations pane (Format Visual Section) , scroll down to the “Row Subtotal” & “Columns Subtotal” sections.
  3. Play with the “On” and “Off” buttons to show & hide Subtotal. Refer video for more details.

Summarize and Show Value As an Option

  1. Select the matrix visual that you want to modify.
  2. In the Visualizations pane(Add data to Visual), scroll down to the “Values” & Right click on the field which you want to summarize.
  3. Select options from “Sum”, “Min”, “Max”, “Average”, “Count” etc.

Conditional Formatting

  1. Select the matrix visual that you want to modify.
  2. In the Visualizations pane(Add data to Visual), scroll down to the “Values” & Right click on the field in which you want to implement conditional formatting.
  3. Select options from “Background Color”, “Font Color”, “Data Bars”, “Icons”, or “URL”.

Filtering and Slicing Data Pivot table Data in Power BI

Three options are available to filter Data in the Power Bi Pivot table:

  1. Use Slicers in power bi to filter the data.
  2. Use the “Filter on this Visual” option
  3. Use Dax for Filtering the data by using DAX functions like Calculate, Filter, etc.

Create Pivot Tables in Power BI using Query Editor

Power Query is a powerful data transformation tool that is built into Power BI. It allows you to pivot and unpivot columns with ease. Here are the steps to pivot and unpivot columns in Power Query:

  1. Open your Power BI Desktop and go to the “Home” tab.
  2. Click on the “Transform Data” button to launch Power Query.
  3. In Power Query, select the column(s) that you want to pivot.
  4. Go to the “Transform” tab and click on the “Pivot Column” button.
  5. In the Pivot Column dialog box, select the column that you want to use as the new column headers.
  6. Choose the aggregation function that you want to use for the values.
  7. Rename the new columns if necessary.
  8. Click “OK” to apply the pivot transformation.

Pivot and Un-Pivot Columns in Power BI Table

To unpivot columns in Power Query, follow these steps:

  1. Select the column(s) that you want to unpivot.
  2. Go to the “Transform” tab and click on the “Unpivot Columns” button.
  3. In the Unpivot Columns dialog box, select the columns that you want to unpivot.
  4. Rename the new columns if necessary.
  5. Click “OK” to apply the unpivot transformation.

Once you have completed your transformations, you can load the data back into Power BI by clicking on the “Close & Apply” button. The transformed data will then be available for use in your Power BI reports.

Uses of Pivot Table in Power BI

Pivot tables have many uses in data analysis and reporting. Here are some of the common uses of pivot tables:

  1. Data summarization: Pivot tables allow you to summarize large amounts of data quickly and easily. You can use them to group data by different categories and calculate summary statistics, such as sums, averages, and counts.
  2. Data exploration: Pivot tables enable you to explore your data in different ways, such as by filtering, sorting, and grouping. This can help you identify trends, patterns, and outliers in your data.
  3. Data comparison: Pivot tables allow you to compare data across different categories and time periods. For example, you can compare sales data for different products, regions, and time periods to identify trends and patterns.
  4. Data visualization: Pivot tables can be used to create charts and graphs that visualize your data. This can help you communicate your findings more effectively to others.
  5. Ad hoc reporting: Pivot tables enable you to quickly create ad hoc reports that meet your specific needs. You can easily change the layout and format of the table to suit your requirements.

Conclusion

  1. If you want to Pivot and Unpivot columns before creating the report use Power query in Power BI.
  2. if you want to create a Pivot table similar to excel in your report then create a matrix visual with slicers in Power BI desktop.