Question

How do I pivot and unpivot columns dynamically in Power BI?
Dynamic pivoting and unpivoting are essential for reshaping data, particularly when dealing with flexible or evolving schemas. This question addresses methods in Power Query to perform these operations dynamically and efficiently, while minimizing manual effort and avoiding performance degradation in large models.

Answer 1

To dynamically pivot or unpivot columns in Power BI, utilize Power Query:

• Click "Unpivot Other Columns" after selecting the columns you wish to retain as identifiers. Wide tables are transformed into long format, which is perfect for analysis.
• Choose a values column and an attribute column, then use "Pivot Column" to pivot. Select a suitable aggregation, such as count or sum.
• Use "Table.ColumnNames" and M code logic to programmatically reference columns in dynamic scenarios (such as when column names change). By doing this, hard coding is avoided, and schema changes are accommodated.
• On large datasets, use with caution; for optimal performance, combine with filters and make sure query folding is enabled.