Unpivot table

If we look up the newly created table with our Query Tool, we can see that it contains all the Month columns as well as the added YearName column.

However, our budget measure is currently spread across the 12 month columns in a pivoted format. If we try to use these data as they are, we will be getting not only one budget measure, but actually 12 budget measures: JanauryBudget, FebruaryBudget, MarchBudget … etc.

Hence, we need to un-pivot the table.

Note: Initially, the BudgetData table isn’t on the Source Table list. Instead, click the Configure button to manually enter the name of the Source Table.

The Unpivot task needs to be configured:

• Source Table: Manually enter the name of the table you want to unpivot.
• Destination table: The dialog will automatically suggest the same table name, but with a prefix of ‘Unpivot_’.
• Unpivot Column Name: In this case ‘Month’ will be a fitting name in the destination table since we will want to unpivot the month columns.
• Columns to unpivot: We want to manually point out the fields (the columns) that we want to unpivot.
• List: Here, we type in and add the names of columns we want to unpivot. We can do this one at a time, or if we have a simple text file with all the names, we can choose to copy/pate them via the clipboard.

The unpivoted table, when we look at it from the Query Tool at this point:

Notice the ‘Month VALUE’ column, which in fact is our Budget measure column. Once we create the measure in the Data Model Editor, we can apply the proper name to it.