Transform Tab in Query Editor: Pivot and Unpivot column in Power BI: Lesson-7 P-4
TransformTab of Query Editor in Power BI: Pivoting and unpivoting columns : Reshape your data for analysis
Pivot and Unpivot are two powerful data transformation techniques in Power BI that allow you to reshape your data for better analysis and visualization. Here is a breakdown of each concept:
Pivot:
- Purpose: Converts data from a wide format (many columns) to a narrow format (fewer columns) by grouping values based on a selected category.
- Scenario: Imagine a table with sales data for different products over months. Pivoting by "month" will create a single column for each month, with rows representing the products and their respective sales values for each month.
- Advantages:
- Facilitates data analysis and visualization of trends over time or categories.
- Reduces data redundancy and simplifies complex tables.
unpivot:
- Purpose: Reverses the process of pivoting, converting data from a narrow format (few columns) to a wide format (many columns).
- Scenario: Consider a table with separate columns for sales data for different products (Product A, Product B, etc.). These pivots will create a single "Product" column with the product names and another "Sales" column with the corresponding sales values, essentially "packing" the data from the original format.
- Advantages:
- Enables comparison of different metrics across categories or dimensions.
- Useful for creating specific chart types that require data in a specific format.
How to pivot and unpivot in Power BI:
- In the Power Query Editor: Select the table you want to change.
- Pivot: Go to the "Transform" tab and select "Axis Columns". Select the column you want to use for summation and select the values to be pivoted.
- Unpivot: Go to the "Transform" tab and select "Unpivot Other Columns" or "Unpivot Columns" according to your specific needs.
Choosing the right technique:
The decision to pivot or not to pivot depends on the goals of your analysis and the desired data format:
Pivot: Use when you want to analyze trends or compare values across categories.
Unpivot: Use when you need to compare different metrics across categories or create specific chart types.
Remember:
- Pivoting and unpivoting can affect data relationships and calculations. Make sure it remains correct after the change.
- Experiment with both techniques to understand how they reshape your data and choose the one that best suits your analysis needs.
- Consider using the "Preview" option to preview the changed data before applying the changes permanently.
By mastering pivoting and unpivoting, you gain the ability to effectively connect your data, unlock deeper insights, and create informative visualizations in Power BI.
In Short:
In Power BI, the "Pivot" and "Unpivot" column operations are transformations available in the Power Query Editor (also known as "Query Editor"). These operations allow you to reshape your data, especially when working with tables, by rotating rows into columns (Pivot) or columns into rows (Unpivot).
Lab work
- Load data
- Home->Get data ->Excel Workbook
- Open Query Editor
- Home -> Transform data -> Transform data (From Query editor menu)
- Select Transform option from Query editor Menu
Then you have
1:- Unpivot Other Columns
- convert table data from columns into rows
- fist of all select required column from table
- select transform from maim Menu
- click on Unpivot Columns
- Select Unpivot Other Columns
Then
table view after Unpivot Other Columns
2:- Unpivot Columns
- display title(attribute) with data in every row
for example Unpivot Columns according to srno
Unpivot Columns according to 1st Name
3:- Unpivot Only Selected Columns
- select only one column (According to srno)
- select Unpivot Only Selected Columns
- Transform -> Unpivot Columns -> Unpivot Only Selected Columns
Final View
if we select only one column the it will show data same like Unpivot columns option
and if we select more than one column then show data different according to selected columns
Select Two columns from table
- After selection of two columns (srno and 1st Name)
table close view (according to two columns)
According to three columns (srno, 1st Name and city)
