The example below shows an unpivot transformation on a very simple data set. In the original data table, there are several columns of data and three rows. Each row contains a Performance Objective, the owner of the Objective and the Goal of that Objective, and actual weekly score for each objective.
While this is certainly useful data, we want to turn all weekly scores into a column by Objective for upload to the Etho application.
After unpivoting the data, we will have one row for each Objective Score, by Objective.
We don’t want to transpose (or unpivot) every column, we only want to unpivot the date columns with the weekly numbers. We also want to see any empty cells in our transposed version. In our example you can see we have Objectives, their owner, the objective goal, and the weekly scores for each of the 3 objectives going back to January 2020.
Here’s how to unpivot the score data.
1. Select All Data within your spreadsheet
2. From the Data Tab select From Table/Range
Click OK to Create Table (Be sure “My table has headers” is checked)
3. Power Query Editor will open with your table
4. Select the Columns you are NOT Unpivoting – (click in the header to select each)
In our Example these columns are Objective, Owner, and Goal.
5. Right Click on the header of one of the selected columns and choose: “Unpivot Other Columns”
6. You will now see your horizontal data sorted vertically in the new table
7. Click: Close and Load
8. Remove the Table Style by clicking:– Table Design – Table Styles – Select None
9. Lastly, you can now save your file as a CSV
