Unpivot Table in Power BI

Power BI Unpivot table option converts selected columns into rows. Let us see how to create an Unpivot table or how to convert the pivot table with an example.

How to create unpivot Table in Power BI

Please click the Edit Queries option under the Power BI desktop Home tab to create or convert the pivot to an unpivot table.

Edit Queries Option 1

Clicking the Edit Queries option opens a new window called the Power BI Power Query Editor.

In this Unpivot Table in Power BI example, we are going to perform on the columns 2005, 2006, 2006, and 2007.

Unpivot Table in Power BI 2

There are a couple of ways to this

The First approach to Unpivot Table in Power BI

Please select the columns that you don’t want to unpivot or you want to keep unchanged will open the context menu. Select the Unpivot Other Columns option. This will be performed on all the fields except the selected ones.

Unpivot Other Columns option 3

Now you can see the unpivot table in Power BI.

Check the auto generated DAX expression 4

The second approach to unpivot Table in Power BI

Please select the columns you want to use and right-click on them to open the context menu. Next, select the Unpivot Only Selected Columns option. This option will be on all the selected columns.

Choose Only Selected Columns Option 5

Now, you can see the unpivoted table.

Unpivot Table in Power BI 6

The third approach

In order to convert the pivot table, go to the Transform Tab. Under this tab, you can find the Unpivot Column button, as shown below.

Go to the Transform Tab 7

Use the drop-down list to perform this operation. We selected the Product ID and Name fields for demonstration purposes and then set the Unpivot Other columns option.

Choose Other Columns Option 8

Now you can see the Power BI unpivot table.

Unpivot Table in Power BI 9

We renamed the cols to more meaningful ones. Next, hit the Close & Apply option under the Home tab to apply these changes.

Rename Column 10

From the screenshot below, you can see those columns on the right side.

Check the Fields 11

Unpivot FAQs

What is Unpivot in Power BI?

Unpivot transfers the columns into attributes and values, which means converting columns to rows. All the column names become row fields, and the associated numbers become values of the corresponding row.
Attribute – Column Names
Values – The values under Attribute column names.

How do I Unpivot data in a Power BI query?

There are multiple ways to unpivot in Power Query. The first is selecting the columns and right-clicking them to open the context menu. Here, choose either of the three options. The second approach is to go to the Transform tab and use the Unpivot columns option.

What is the difference between Pivot and Unpivot in Power BI?

Pivot converts rows into columns, whereas unpivot does the reverse; it converts columns to rows.

How do I Unpivot multiple columns in Power Query?

By using the Ctrl, select the number of columns you want to perform, and right-click on any will open the menu. Next, choose the Unpivot Columns or the only selected option.