excelpowerqueryunpivot

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

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?

enter image description here

Solution

  • 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).

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