Unpivot Bug in Power Query

May 2017 Update: This article describes a bug that is already fixed in Power BI Desktop, May update. You can now use the Unpivot Only Selected Columns, and the Unpivot function will work well. Keep reading if you are using old versions (e.g. in Excel 2016).

In today’s blog post I would like to share with you a new bug, that I recently found in Power Query. It is relevant for all versions of Excel, Power Query and Power BI Desktop November Update 2016.

As some of you are not in the mood to chase some bugs (Happy holidays!!!), you can come back to this post after the holidays. But don’t forget that you may find out an Elf on your Excel/Power BI shelf that is messing around with your unpivoted data.

At this point in your Power BI / Power QUery journey, You could be quite familiar with the Unpivot option in the Query Editor UI, and if you are not familiar with it, you can learn why it is so useful here.

Did you notice after the last time you performed an Unpivot, that the correpsonding forumla is Table.UnpivotOtherColumns and not Table.Unpivot? It was used to help you creating a robust query that will not fail when new columns will be added in a future refresh.

For exmaple, we have the following table, and would like to Unpivot it to Transaction ID, Year and Amount.

Inside the Query Editor we can select the years and click Unpivot Columns. If you are not a stranger to Unpviot, you would probably suggest instead, to perform Unpivot Other Columns on the Transaction ID column.

And you are right. On most of the cases, the columns that you need to pivot are more dynamic in nature. Like in our exmaple – We will have a new column for 2017 soon,  and it will be better to perform Table.UnpivotOtherColumns on the constant column. That is the reason why the Query Editor will generate the Table.UnpivotOtherColumns as shown below, even if you select Unpivot Columns and not Unpivot Other Columns in the UI.

But what if the columns you want to Unpivot are static and all other columns can change? M allows you to perofrm explicit Unpivot on the columns (See reference here).

Unfortunately, when you try using Table.Unpivot, make sure that the order of the columns you specify in the second argument, will match the order fo your columns in your external data source or current workbook table. You should also ensure that the order of the columns will never change. Othwerwise, you will get the following bug:

Would you like to see the bug?

Download this workbook. Edit the query Table1, and you’ll see in following code in the Query Editor’s Advanced Editor window:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.Unpivot(Source, {"Column7", "Column6", "Column5", "Column4", "Column3", "Column2", "Column1"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

The second argument of Table.Unpivot contains a list of column names, starting from Column7 and going backwards to Column1. But, our table contains the columns Column1, Column2, … Column7 in the correct order. As a result, you will get a wrong association of values.

Note: In the expression above, I added the column names in a reverse order to reproduce the bug, but in real life, you may get the same error when your external data source will suddenly get a different column order.

Prognosis?

Microsoft confirmed the bug, so let’s hope it will be an easy fix for the team (so they will release it soon). Till it is fixed, you can fix it yourself by the advance code below, which uses Table.UnpivotOtherColumns with List.Difference.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(
        Source,
        List.Difference(
            Table.ColumnNames(Source),
            {"Column7", "Column6", "Column5", "Column4", "Column3", "Column2", "Column1"}
        ),
        "Attribute",
        "Value")
in
    #"Unpivoted Columns"