How it works...

While the code looks fairly long, this recipe is relatively straightforward. Reading the DAX from the inside out, we start by using SELECTCOLUMNS statements to return all of the rows in each of the columns we wish to unpivot, that is, Column1, Column2, Column3, Column4, Column5 and Column6, along with the Item column and the Date column, which are the two columns that we do not wish to unpivot. Crucially, for this technique to work, each SELECTCOLUMNS statement must include the same number of columns. In addition, the column names must be the same between all of the SELECTCOLUMNS statements.

Because all of the unpivoted columns have been given the same column name coming out of the SELECTCOLUMNS statements, Column1, when we UNION all of the tables resulting from the SELECTCOLUMNS statements, all of the values from the unpivoted columns become row values within the same column, Column1. Because we have included our Item and Date columns in every SELECTCOLUMNS statement, those columns are not unpivoted.

Now, we can use FILTER to filter out rows where the values from the unpivoted columns are empty and then use one last SELECTCOLUMNS statement to return friendly column names.