In Power BI and Excel, data often comes in a wide format with multiple columns representing different categories. While this format may look structured, it’s not ideal for analysis and visualization.

✅ Solution: The Unpivot feature in Power Query helps restructure data by converting columns into rows, making it easier to perform calculations, filter data, and create visuals.

In this guide, you’ll learn:
✔️ What Unpivoting is and why it’s important.
✔️ Step-by-step instructions to Unpivot data in Power Query.
✔️ Real-world examples to improve data analysis.

1. What is Unpivoting in Power Query?

Unpivoting is the process of converting column-based data into a row-based format. This transformation makes data easier to analyze, especially in Power BI.

📌 Example: Before & After Unpivoting

🚀 After Unpivoting:

This new structure allows easier filtering, grouping, and analysis in Power BI.

2. Why Use Unpivot in Power Query?

✔ Makes data more structured – Ideal for pivot tables and Power BI visuals.
✔ Improves filtering and calculations – Helps in running SUM(), AVERAGE(), and COUNT() operations.
✔ Works better with DAX and Power BI measures – Reduces the complexity of calculations.
✔ Enhances performance – Avoids redundant columns and reduces dataset size.

3. How to Unpivot Data in Power Query (Step-by-Step)

📌 Step 1: Load Data into Power Query

1. Open Power BI or Excel.
2. Select your dataset and click Transform Data (Power BI) or Get & Transform → Power Query Editor (Excel).

📌 Step 2: Select Columns to Unpivot

1. In Power Query Editor, select the columns that need unpivoting (e.g., Jan Sales, Feb Sales, Mar Sales).
2. Click on the Transform tab → Select Unpivot Columns.

📌 Step 3: Rename the Columns

• Power Query will create two new columns:
  ✔ Attribute Column → Contains the original column headers (e.g., months).
  ✔ Value Column → Stores the actual data (e.g., sales figures).
• Rename the columns appropriately (e.g., “Month” and “Sales”).

📌 Step 4: Close & Load Data

• Click Close & Load to apply the transformation and return the structured data to Power BI or Excel.

🚀 Your data is now in the ideal format for analysis!

4. Real-World Examples of Unpivoting

Example 1: Sales Data Across Multiple Periods

Before Unpivoting (Wide Format)

After Unpivoting (Long Format)

This new format allows easier trend analysis and visualization in Power BI dashboards.

5. Unpivot Multiple Columns at Once

If your dataset has multiple sections (e.g., Sales, Expenses, Profits), you can Unpivot multiple columns at the same time.

✔ Steps:

1. Select all columns to Unpivot (Ctrl + Click multiple columns).
2. Click Transform → Unpivot Columns.
3. Rename new columns appropriately.

6. Handling Issues After Unpivoting

📌 1. Replacing Null Values

• If your dataset has empty cells, Power Query may replace them with null values.
• To replace them:
  ✔ Click on the Value Column → Transform → Replace Values → Enter null → Replace with 0 or "N/A".

📌 2. Correcting Data Types

• After Unpivoting, ensure the Value Column has the correct data type (Numbers, Dates, Text).
• Click on the column → Change Data Type (e.g., Decimal Number, Date).

7. When NOT to Use Unpivoting

❌ When each column represents distinct, unrelated categories (e.g., separate product names).
❌ If unpivoting causes data duplication or inconsistency.
❌ When the original column format is necessary for a specific type of analysis.

Conclusion

🚀 Power Query’s Unpivot feature is a game-changer for data transformation in Power BI.

✅ Key Takeaways:
✔ Unpivoting converts wide tables into structured, row-based data.
✔ Step-by-step process ensures clean, analyzable data.
✔ Works best for time-based, categorical, and pivoted datasets.
✔ Avoid unpivoting when it distorts unique values.

By mastering Unpivot in Power Query, you can enhance your reporting, improve data accuracy, and optimize Power BI dashboards!