Power Query - Unpivot & Pivot Tools

• They are used to fix bad layouts and convert them to good layouts without changing the source
• Bad Layouts:
◇ Yearly amounts
◇ Single column with multi data types
◇ Headers in multiple rows
◇ Repeating rows
• Good Layout: in tabular form

• Example:

images/162-1.png

◇ Source data should not have any total columns
◇ Yearly amounts should be in separate columns: Year + Amount

images/162-2.png

• Refer to file “power_query_unpivot_yt.xlsx” for step-by-step instructions

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Commands

/* Open source (from Excel Window)*/
Data > From Table / Range

/* Unpivot and related commands */
/* (from Power Query Editor) */
Transform > icon Unpivot
Transform > Split Column
Transform > Format > Trim
Transform > Pivot Column
Transform > Tranpose
Transform > Fill > Down
Transform > Merge Columns
(icon on the left of a column)Use first row as headers
Add Column > Index Column
Transform > Fill > Up
File > Close & Load

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Source

• https://youtu.be/-IMqkg35adA

Index