What's the difference between "Unpivot Columns" and "Unpivot Only Selected Columns" in Power Query?

Question

When I select multiple rows and right click in Microsoft Power Query, (in Excel 2016), I see two similar options for unpivoting: "Unpivot Columns" and "Unpivot Only Selected Columns".

Both seem to give the same result when used, flattening the selected columns and putting them into a single column. Since unpivoting already only applies to the selected columns, the second option seems redundant.

According to Microsoft's documentation, "Unpivot Only Selected Columns" should be used when "you don’t know the number of columns in the data source". However, I don't know why the number of columns would change the behavior of either option.

What's the difference between "Unpivot Columns" and "Unpivot Only Selected Columns"? When would I want to use one over the other?

Sam Nseir · Accepted Answer · 2025-07-09 03:22:36Z

2

Take a table that has columns A, B, C, & D.

Case 1: With column A selected and Unpivot Columns selected, generates:

Table.UnpivotOtherColumns(#"Changed Type", {"B", "C", "D"}, "Attribute", "Value")

Case 2: With column A selected and Unpivot Only Selected Columns selected, generates:

Table.Unpivot(#"Changed Type", {"A"}, "Attribute", "Value")

In terms of transformation result, both yield the same, however the execution is different. What if new columns are introduced from the source (like log csv file).

With Case 1: Column A and the new columns will be Unpivoted.
(Also if columns B, C, or D are no longer present then it will throw an error.)
With Case 2: Column A will be unpivoted, only.

Lastly, selecting columns B, C, & D and selecting Unpivot Other Columns yields the same result as Case 1 (inverse selection & inverse action).

edited Jul 9 at 3:22

answered Jul 8 at 17:51

Sam Nseir

13.1k2 gold badges5 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

What's the difference between "Unpivot Columns" and "Unpivot Only Selected Columns" in Power Query?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related