What is unpivot and when would you use it? The unpivot operation converts columns to rows. It creates a new column that corresponds to the headers of the rows you want to unpivot. You may need to do this if you get data from another source that is not in the format you’d like. For example, you might import data that has an item on the left and a series of times as columns you need to unpivot. You might even import data that has a comma-separated set of values within a single column. What do these examples look like?

Perhaps you have products in different categories. The categories were originally in a single cell separated with a comma. You used Power Query to separate the categories with the

Going back to the first example with our products. First, make the data a table, and name the table. If you prefer to use a range, highlight the range and give it a name. Go to the Data tab and in the Get and Transform section, click From Table/Range.

Home tab, Close and Load to a Table. You get a new worksheet with the data in it. The worksheet’s name is the same as the name you gave in Power Pivot.Note the red underlines in the above diagram.

We’ve got a new column called Attribute.

Another Example

Let’s look at the second example mentioned at the top of the post. Here we have products and the categories they belong to where aech product, movie, actually belongs to multiple categories or genres. The unpivot works in a similar way here as it did in the previous example. Below is a screenshot of the raw data.

YouTube

Here is a video on unpivotting using Power Query called Convert Columns to Rows in Excel (NO Transpose, NO Formulas – SIMPLY UNPIVOT in Power Query) by Leila Gharani. In case your data has multiple headers, have a look at the video called Excel Unpivot Data with Multiple Headers (Multiple Row levels into Columns with Power Query) also by Leila.

Udemy.com

There is a course at Udemy.com called Microsoft Excel: Business Intelligence w/ Power Query & DAX. Video 23 has another example of unpivoting and pivoting a very small sample of data.