Hey team,
This might be an odd request and so far I've been unable to get some good Google results based on the fact I'm not sure how to ask this question.
Long story short: we run NPS surveys across our business and get results from independent partners. All partners but 1 provide the NPS data in survey form, where each row is a single response and each column represents a different question.
We're trying to come up with a combined target measure for the group so I'm left with the challenge to add the remaining partner to the totals but the format is throwing me off. The format of 95% of the data is as follows:
| RespID | NPS score | Date |
| 1 | 8 | 1/04/2021 |
| 2 | 8 | 1/04/2021 |
| 3 | 3 | 1/04/2021 |
| 4 | 10 | 1/04/2021 |
But the format of the 1 partner is as follows. For some horrible reason it has the scores in the first column, then the count of said scores within the date range in the 2nd column.
Now my question is: is there any way I could somehow transform the below table into a format that is similar to the first table so I can append? Ie, create an table with an index and have a row count that is equal to the sum of the 2nd column (7 in this case) with 6x10 and 1x5?
| Score | 4/2021 |
| 10 | 6 |
| 9 | 0 |
| 8 | 0 |
| 7 | 0 |
| 6 | 0 |
| 5 | 1 |
| 4 | 0 |
| 3 | 0 |
| 2 | 0 |
| 1 | 0 |
| 0 | 0 |
